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

database-designMySQL

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:

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

Best Answer

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.

Related Solutions

How to properly design a many-to-many (charges/payments) accounting system

So, my question -- officially -- is what is the best way to go about tracking/reconciling payments, and charges, while still allowing for overpayments, etc.

This is the problem. There are several styles of accounting. Are you using double-entry? I would recommend learning double-entry accounting. It's complicated but it works.

Is this for an actual assignment or for learning?

Here's a system that may be easy enough to dig into to give you the concepts you need to move forward? I know the author (by way of this podcast but not personally) but I haven't looked at the codebase. I just know that it does DE accounting.

http://twit.tv/floss150

Need help designing table with list of IDs to store

Do not use a list of ids. You will regret it. Use junction tables.

DDL:

create table organization (
  organization_id bigserial primary key,
  name text not null
);

create table individual (
  individual_id bigserial primary key,
  email_address varchar(255) not null
);

create table organization_member (
  organization_id bigint references organization(organization_id),
  individual_id bigint references individual(individual_id),

  primary key (organization_id, individual_id)
);

create table email_message (
  email_message_id bigserial primary key,
  send_at datetime not null,
  subject varchar(80),
  body text
);

create table email_message_organization (
  email_message_id bigint references email_message(email_message_id),
  organization_id bigint references organization(organization_id),

  primary key (email_message_id, organization_id)
);

DML:

-- add an org:

insert into organization (name) values ('Acme, Inc');

-- add an individual:

insert into individual (email_address) values ('user@example.com');

-- associate individual w org:

insert into organization_member values (1,1);

-- add an email message:

insert into email_message (send_at, subject) values ('2013-07-31 12:12:12', 'test');

-- associate an email message with an org:

insert into email_message_organization values (1, 1);

-- when it's time to send the email, get the right people:

select 
e.subject, 
e.body, 
i.email_address 
from individual i 
join organization_member om using (individual_id) 
join email_message_organization emo using (organization_id) 
join email_message e using (email_message_id);

-- send the emails, record that they were sent in another table

You should use the Party model for representing individuals and organizations, which is not what I used, but you should still look it up.

Best Answer

Related Solutions

How to properly design a many-to-many (charges/payments) accounting system

Need help designing table with list of IDs to store

Related Question