I have a situation where a particular child object may have multiple parents of different types. For example, a foo object may be a child of one ore more of a, b, or c objects. More over, I also have a bar object which may also be a child of one or more a, b, or c objects. What's the proper way to model this in SQL?
A) Single table for all relationships:
relationship_tbl
parent_id parent_type child_id child_type
--------- ----------- -------- ----------
1 a 5 foo
2 a 6 foo
3 c 7 bar
4 b 7 bar
B) Unique table for each parent type:
a_child_tbl
parent_id child_id child_type
--------- -------- ----------
1 5 foo
2 6 foo
b_child_tbl
parent_id child_id child_type
--------- -------- ----------
4 7 bar
c_child_tbl
parent_id child_id child_type
--------- -------- ----------
3 7 bar
C) Unique table for each child type:
foo_parent_tbl
child_id parent_id parent_type
--------- ----------- -----------
5 1 a
6 2 a
bar_parent_tbl
child_id parent_id parent_type
--------- ----------- -----------
7 3 c
7 4 b
D) Unique table for each combination
a_foo_tbl
parent_id child_id
--------- --------
1 5
2 6
b_bar_tbl
parent_id child_id
--------- --------
4 7
c_bar_tbl
parent_id child_id
--------- --------
3 7
E) Some other strategy that I haven't explored
To me, it seems like A would be the easiest to query and answer questions like Find all the parents of child 7 or Find all the children of parent 4, but I've read some advice that basically says never create generic tables for parent/child relationships.
Could somebody shed some light on the best way to do this and why? It's safe to assume that the table will never have more than a few million rows in it.
I'd recommend a variation of your solution C. You need to have a separate M:N table for each M:N relationship, per Fourth Normal Form.
But also create a supertable to unify all the a, b, c parent types, so that the M:N tables can reference a single table, in which each parent_id is strictly assigned its respective type.
parent_tbl
parent_id parent_type
--------- -----------
1 a
2 a
3 c
4 b
a_parent_tbl
parent_id parent_type
--------- -----------
1 a
1 a
b_parent_tbl
parent_id parent_type
--------- -----------
4 b
c_parent_tbl
parent_id parent_type
--------- -----------
3 c
In each sub-parent table, the parent_type is restricted to a single value. The parent_table has a unique constraint on (parent_id, parent_type), and the foreign key in each sub-parent tables references the columns in that unique constraint. Therefore no parent_id can be referenced by more than one sub-type.
Then your child M:N tables only need to reference parent_table by ID. You don't necessarily need a parent_type column in these tables, but you do if you need to create a UNIQUE constraint over (child_id, parent_type) so that each child can have only one parent of a given type.
foo_parent_tbl
child_id parent_id
-------- ---------
5 1
6 2
bar_parent_tbl
child_id parent_id
-------- ---------
7 3
7 4
You may also like to read: