I have worked with T-SQL in multiple DBs, but I have never designed a database before. I am designing a new database in MySQL which will track humans and organizations, including financial transactions received from them. I would like to easily be able to treat them as one group for the purpose of joining to other tables, such as tables tracking charges applied to their balances, payments made by them, and so on.
I have seen other databases track both humans and organizations in a single table, but since they are so unlike, this always seemed sloppy to me. For example, a human should have a last name that is NOT NULL
, but an organization has no last name, so you end up needing a column just to track whether it's a human or an organization and then code to enforce rules based on that column's value. Querying from such tables also requires more code.
I would like to create a human
table with the PK being human.id_number
and an organization
table with the PK being organization.id_number
. I want to then somehow funnel all of the id_number
s into a third table, let's just call it constituent
for this question. I then want to join other tables in the DB to constituent
, not to human
or organization
. For example, some constituent
primary key, maybe constituent.id_number
, would join to payment.constituent_id_number
.
The problem is that I'm not sure how to funnel human.id_number
and organization.id_number
into a single table. I have two questions:
- Is my method a good choice? Is there a better option I should use?
- How can I accomplish what I've described above?
AUTO_INCREMENT
, see the functionlast_insert_id()
.