title: Proper way to model M:N relationship in SQL where parent may be one of many types
body: <p>I have a situation where a particular child object may have multiple parents of different types. For example, a <code>foo</code> object may be a child of one ore more of <code>a</code>, <code>b</code>, or <code>c</code> objects. More over, I also have a <code>bar</code> object which may also be a child of one or more <code>a</code>, <code>b</code>, or <code>c</code> objects. What's the proper way to model this in SQL?</p>
  
  <p>A) Single table for all relationships:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>B) Unique table for each parent type:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>C) Unique table for each child type:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>D) Unique table for each combination</p>
  
  <pre><code>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
  </code></pre>
  
  <p>E) Some other strategy that I haven't explored</p>
  
  <p>To me, it seems like A would be the easiest to query and answer questions like <code>Find all the parents of child 7</code> or <code>Find all the children of parent 4</code>, but I've read some advice that basically says never create generic tables for parent/child relationships.</p>
  
  <p>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.</p>