Source
r/PHPhelp•
mrthesis

Database table design

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).

Posted Dec 5, 2018, 9:02 AM UTC

13
cryptichorizon77•

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

5
tutreak•

this, but there's 3 solutions to this:

you merge them, add a new column that tells this is of type B or A, that way there will be no overlapping ids for C to mess up with due to auto_increment.

keep A and B separate tables, add another column to C that tells its id is referencing either A or B

You can also go hard mode and and generate a unique id for A or B, by selecting MAX from both tables, then use the max value from both of those, add 1, then insert that, then use that id in C, then when you select from C, you can left join on both tables, and you should get a hit, obviously this is the worst solution of all time, a lot of overhead and no real way to optimize for proper indexing and this method will get increasingly slower as the tables sizes grow.

My preferred solution would be: 1

2
liquid_at•

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.

​

2