title: Foreign key pointing to different tables
body: <p>I'm implementing a <em>table per subclass</em> design I discussed in a <a href="https://stackoverflow.com/questions/2714206/database-design-objects-with-different-attributes">previous question</a>. It's a product database where products can have very different attributes depending on their type, but attributes are fixed for each type and types are not manageable at all. I have a master table that holds common attributes:</p>
  
  <pre><code>product_type
  ============
  
  product_type_id INT
  product_type_name VARCHAR
  
  E.g.:
  
  1 'Magazine'
  2 'Web site'
  
  product
  =======
  
  product_id INT
  product_name VARCHAR
  product_type_id INT -&gt; Foreign key to product_type.product_type_id
  valid_since DATETIME
  valid_to DATETIME
  
  E.g.
  
  1 'Foo Magazine'      1 '1998-12-01' NULL
  2 'Bar Weekly Review' 1 '2005-01-01' NULL
  3 'E-commerce App'    2 '2009-10-15' NULL
  4 'CMS'               2 '2010-02-01' NULL
  </code></pre>
  
  <p>... and one subtable for each product type:</p>
  
  <pre><code>item_magazine
  =============
  
  item_magazine_id INT
  title VARCHAR
  product_id INT -&gt; Foreign key to product.product_id
  issue_number INT
  pages INT
  copies INT
  close_date DATETIME
  release_date DATETIME
  
  E.g.
  
  1 'Foo Magazine Regular Issue'      1 89 52 150000 '2010-06-25' '2010-06-31'
  2 'Foo Magazine Summer Special'     1 90 60 175000 '2010-07-25' '2010-07-31'
  3 'Bar Weekly Review Regular Issue' 2 12 16  20000 '2010-06-01' '2010-06-02'
  
  item_web_site
  =============
  
  item_web_site_id INT
  name VARCHAR
  product_id INT -&gt; Foreign key to product.product_id
  bandwidth INT
  hits INT
  date_from DATETIME
  date_to DATETIME
  
  E.g.
  
  1 'The Carpet Store'        3 10  90000 '2010-06-01'         NULL
  2 'Penauts R Us'            3 20 180000 '2010-08-01'         NULL
  3 'Springfield Cattle Fair' 4 15 150000 '2010-05-01' '2010-10-31'
  </code></pre>
  
  <p>Now I want to add some fees that relate to one specific item. Since there are very little subtypes, it's feasible to do this:</p>
  
  <pre><code>fee
  ===
  
  fee_id INT
  fee_description VARCHAR
  item_magazine_id INT -&gt; Foreign key to item_magazine.item_magazine_id
  item_web_site_id INT -&gt; Foreign key to item_web_site.item_web_site_id
  net_price DECIMAL
  
  E.g.:
  
  1 'Front cover'      2 NULL 1999.99
  2 'Half page'        2 NULL  500.00
  3 'Square banner' NULL    3  790.50
  4 'Animation'     NULL    3 2000.00
  </code></pre>
  
  <p>I have tight foreign keys to handle cascaded editions and I presume I can add a constraint so only one of the IDs is NOT NULL.</p>
  
  <p>However, my intuition suggests that it would be cleaner to get rid of the item_WHATEVER_id columns and keep a separate table:</p>
  
  <pre><code>fee_to_item
  ===========
  
  fee_id INT -&gt; Foreign key to fee.fee_id
  product_id INT -&gt; Foreign key to product.product_id
  item_id INT -&gt; ???
  </code></pre>
  
  <p>But I can't figure out how to create foreign keys on item_id since the source table varies depending on product_id. Should I stick to my original idea?</p>
  
  <h2>Update</h2>
  
  <p>The alternative I was actually considering is:</p>
  
  <pre><code>fee
  ===
  
  fee_id INT
  fee_description VARCHAR
  product_id INT -&gt; Foreign key to product.product_id
  item_id INT -&gt; ???
  net_price DECIMAL
  </code></pre>
  
  <p>I'm not sure why mentioned a separate <code>fee_to_item</code> table (I guess I was thinking of something else) but it doesn't really change the question since the key point is the same: <code>foo1_id+foo2_id+foo3_id</code> vs <code>source_id+foo_id</code></p>