How to join to a single list of IDs from two different tables

Question

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 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:

1. Is my method a good choice? Is there a better option I should use?
2. How can I accomplish what I've described above?

Answer 1

You contradict yourself. First you state you would like to treat humans and organization as one group and then you state using one table would cause queries to require more code.

Three tables with one master:
With two tables rather than funnel to, the constituentID is the master the [human].[id_number] and [organization].[id_number] would just be FK. So you would insert into constituent first to get the master ID. With this it would be a pain to enforce the same ID not in both [human] and [organization]. If you are going to put a type in constituent to enforce the two other tables then you might as well combine in the first place.

Two tables:
You could have two independent [human] [organization] tables and combine them with with a view with a union but that would not be my preference.

One table:
Not having a last name is not a big difference. They both have address, phone number, and EIN/SSN. I would share name / last name and let first name be null. A person is still valid without a first name so do you really need to enforce it. ID is the PK and is an identity. Type - person or organization is not part of the PK. Maybe SSN/EIN as PK but then that would not allow for one constituent to have more than one account. Then you can have views for person and organization.

In summary I don't think a null (enforced or not) is that big of a deal. Compared to separate tables.

Answer 2

You method is close to how it would typically be done but you are looking at the relation between human/organization and the constituent tables backwards.

A human / organization would have a constituent.

So your human table would have a constituent_id column as would your organization table.

That way most of your code would be able to operate on the constituent table without having to worry about whether the constituent is a human or an organization