SQL Server - database design - one to many OR many to many

Question

I'm wanting advice as to the best way to design my database - it is storing medical data. I have a number of different entities (tables) that may have one or more medications associated with them. These are always 1 to many relationships, and the medications are only ever related to a single entity (ie. they are not shared). The columns for the Medication data are common.

My question is, should I have a single Medication table (and use numerous many-to-many mapping tables) OR should I use multiple Medication tables?

Option 1 - single Medication table:

[table1]1---*[table1_has_medication]*---1[medication]
[table2]1---*[table2_has_medication]*---1[medication]
[table3]1---*[table3_has_medication]*---1[medication]

Option 2 - multiple Medication tables:

[table1]1---*[table1Medication]
[table2]1---*[table2Medication]
[table3]1---*[table3Medication]

Option 1 seems neater as all Medication data is in a single table. However, a Medication is in fact only ever related to a single table so it's not a true many-to-many relationship. Also, I assume I can't support cascaded deletes for many-to-many relationships so I need to be careful of "orphaned" Medication records.

I'm interested in the opinions of experienced database designers. Thank you.

Answer 1

In addition to not representing your requirements accurately, a single many-to-many (aka. "junction" or "link") table has another problem: one FK can only reference one table, so either you'll have to use multiple exclusive FKs, or you'll have to enforce referential integrity yourself, which is harder to do properly than it looks.

All in all, looks like separate medication tables are what you need.

NOTE: That could potentially become a problem if your requirements evolve and you suddenly have to reference all medications from another table. If that happens, consider "inheriting" all medication tables from the common table. Here is an example you can extrapolate from.

Answer 2

Found a suitable answer on DBA stackexchange.

Repeated below:

Relational databases are not built to handle this situation perfectly. You have to decide what is most important to you and then make your trade-offs. You have several goals:

• Maintain third normal form
• Maintain referential integrity
• Maintain the constraint that each account belongs to either a corporation or a natural person.
• Preserve the ability to retrieve data simply and directly

The problem is that some of these goals compete with one another.

Sub-Typing Solution
You could choose a sub-typing solution where you create a super-type that incorporates both corporations and persons. This super-type would probably have a compound key of the natural key of the sub-type plus a partitioning attribute (e.g. customer_type). This is fine as far as normalization goes and it allows you to enforce referential integrity as well as the constraint that corporations and persons are mutually exclusive. The problem is that this makes data retrieval more difficult, because you always have to branch based on customer_type when you join account to the account holder. This probably means using UNION and having a lot of repetitive SQL in your query.

Two Foreign Keys Solution
You could choose a solution where you keep two foreign keys in your account table, one to corporation and one to person. This solution also allows you to maintain referential integrity, normalization and mutual exclusivity. It also has the same data retrieval drawback as the sub-typing solution. In fact, this solution is just like the sub-typing solution except that you get to the problem of branching your joining logic "sooner".

Nevertheless, a lot of data modellers would consider this solution inferior to the sub-typing solution because of the way that the mutual exclusivity constraint is enforced. In the sub-typing solution you use keys to enforce the mutual exclusivity. In the two foreign key solution you use a CHECK constraint. I know some people who have an unjustified bias against check constraints. These people would prefer the solution that keeps the constraints in the keys.

"Denormalized" Partitioning Attribute Solution
There is another option where you keep a single foreign key column on the chequing account table and use another column to tell you how to interpret the foreign key column (RoKa's OwnerTypeID column). This essentially eliminates the super-type table in the sub-typing solution by denormalizing the partitioning attribute to the child table. (Note that this is not strictly "denormalization" according to the formal definition, because the partitioning attribute is part of a primary key.) This solution seems quite simple since it avoids having an extra table to do more or less the same thing and it cuts the number of foreign key columns down to one. The problem with this solution is that it doesn't avoid the branching of retrieval logic and what's more, it doesn't allow you to maintain declarative referential integrity. SQL databases don't have the ability to manage a single foreign key column being for one of multiple parent tables.

Shared Primary Key Domain Solution
One way that people sometimes deal with this issue is to use a single pool of IDs so that there is no confusion for any given ID whether it belongs to one sub-type or another. This would probably work pretty naturally in a banking scenario, since you aren't going to issue the same bank account number to both a corporation and a natural person. This has the advantage of avoiding the need for a partitioning attribute. You could do this with or without a super-type table. Using a super-type table allows you to use declarative constraints to enforce uniqueness. Otherwise this would have to be enforced procedurally. This solution is normalized but it won't allow you to maintain declarative referential integrity unless you keep the super-type table. It still does nothing to avoid complex retrieval logic.

You can see therefore that it isn't really possible to have a clean design that follows all of the rules, while at the same time keeping your data retrieval simple. You have to decide where your trade-offs are going to be.