Database table design

1 points · submitted Dec 2018 by u/mrthesis

I have two parent tables, A and B and a child table C which has a relationship with either A or B. How do I best express this?

The tables is something like this:

 A: id, text
 B: id, text
 C: id, sub_text

Adding two columns, a_id, b_id being nullable on C seems like a waste as only one of them will be set. I could add two columns parent_id and parent_table_name, but I would like some sort of foreign key relationship where the child data is deleted, and then this wont work (I think).

2 comments · 100% upvoted · other discussions

comments

cryptichorizon77 6 points · Dec 2018

Just curious, is there any good reason why A and B are seperate tables?

permalink

liquid_at 2 points · Dec 2018

If you want to store both IDs, you'll probably also have to store what type of table it is referring to.

so it would be C: id, table, sub_text.

You could also combine A and B by adding a 3rd table specifying the type.

If you plan to automate deletion, the second variant is probably easier, since you only got 2 tables.

permalink