Proper way to model M:N relationship in SQL where parent may be one of many types

Question

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.

How many types are possible? Is it a finite set, or basically an unbounded domain ? — wildplasser, Jul 15 '13 at 23:19
It's definitely a finite set and static. I'm looking at about 5 different types of children that could have one or more of 5 different types of parents. — Bill, Jul 15 '13 at 23:22
In that case: give them all a separate FK, and add a constraint that only one FK can be non-null. Maybe a bit wasteful, but clean. (and in the case of a junction-table: you'll probably need a surrogate. Sigh) — wildplasser, Jul 15 '13 at 23:23

Bill Karwin · Accepted Answer · 2022-10-21T23:32:38.713

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:

My past answers to questions on polymorphic-associations.
My presentation Practical Object Oriented Models In SQL
My book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming

Doesn't parent_tbl contain all the information in a_parent_tbl, b_parent_tbl and c_parent_tbl, whats the need for these three? — A.O., Jul 15 '13 at 23:36
In case each sub-type has its own specific attributes that the others don't have. If that's never going to happen, then I suppose you don't need those sub-type tables. — Bill Karwin, Jul 15 '13 at 23:37
So if I want to find all of the children of a particular parent I need to query N tables, one for each possible child type. Is that correct? — Bill, Jul 15 '13 at 23:58
In this model, yes. I recommend this model because it's normalized correctly and minimized the chances for data anomalies. — Bill Karwin, Jul 16 '13 at 00:00

score 0 · Answer 2 · answered Jul 15 '13 at 23:22

As long as types a,b,c,d are always parents, and types foo and bar can only be children (i.e. 'foo' could/will never be a parent of 'c') You should have a table for each entity and a table for the relationships. One table for parents (parent_id, parent_type), One table for children (child_id, child_type), and One table for relationships (parent_id, child_id).

Proper way to model M:N relationship in SQL where parent may be one of many types

2 Answers2

Linked