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_numbers 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
organization. For example, some
constituent primary key, maybe
constituent.id_number, would join to
The problem is that I'm not sure how to funnel
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?