Database Design: I have a child table which might be used from more than one parent table (one-to-one). How do I design its schema

user_5106170 0
Viewed 204

Imagine I have a child table called offers. offers can be referred from multiple tables such as purchase, membership, etc. Notice that in future, there can be more tables it will be referenced from.

Purchases and memberships are already present in the database and they will always be created before the corresponding offer object. Think of it like a purchase X may provide an offer Y (one to one)

In order to implement this in a relational schema, I thought of two possible options.

Option 1

offers table will not have any references. instead, purchase table and membership table will have a column named offer_id which references the offer table. As given below

   table offers { 
      id,
      offer_name, 
   }

   table purchase { 
      id,
      ..other fields,
      offer_id fk(offers),
   }

   table membership { 
      id,
      ..other fields,
      offer_id fk(offers),
   }

Option 2

offers table will contain a type field which refers the type of offer (purchase, membership, etc..) and there will be multiple nullable foreign keys referring to each table.

   table offers { 
      id,
      offer_name, 
      type enum(purchase, membership,
      purchase_id fk(purchases) NULL, 
      membership_id fk(memberships) NULL,
   }

   table purchase { 
      id,
      ..other fields,
   }

   table membership { 
      id,
      ..other fields,
   }

I felt that Option 1 is the simplest way to do it and also the right way. But it feels like going against database design principles (Parent should not refer child, etc..).

What should be the right way to do this? If I go with option 1, what will go wrong?

database-design mysql
1 Answers

Since you create the offer entity after the purchase or membership entities, offer is the child entity and should therefore have the foreign key(s) to the parent table.

I know it is tempting to do it the other way around, but semantics are important in a database design:

  1. If your FK is in the parent entities, then you run the risk of having orphan offers, that do not belong to any entities. You cannot enforce integrity through FKs.
  2. You cannot prevent through standard database techniques that 1 offer is assigned to multiple entities. You need to write additional application code to enforce this rule.
  3. Anybody looking at your ERD will come to the wrong initial conclusion regarding the nature of the relationship, so you have to provide additional explanation to every new colleague.
  4. If you use an ORM on top of your database schema, the ORM would again interpret the relationship incorrectly, setting up classes and relationships following the FKs. Again, additional explanations will be necessary.

You have three options designing the foreign key(s) in the offer table to follow option 2:

  1. Each parent has a separate FK field. Advantage is that it is clear which parent entity the child belongs to and it is easy to enforce referential integrity. Drawback is that you need to create as many fields as parent entities and requires an alter table to add a new one. Works well, if you have a limited and stable number of parent entities.
  2. Use a type and a parent_id field combination. The type field tells you which parent the record belongs to. This is the opposite of the previous solution - flexible to add new parent types, but enforcing referential integrity through database tool will not be simple!
  3. Use separate offer tables for each parent type. This design can be useful if you plan to diverge the various offers by type.
user_53899970
Related