Foreign key pointing to different tables

Posted by Álvaro G. Vicario on Stack Overflow See other posts from Stack Overflow or by Álvaro G. Vicario
Published on 2010-04-27T12:33:15Z Indexed on 2010/04/27 12:53 UTC
Read the original article Hit count: 242

Filed under:
|
|

I'm implementing a table per subclass design I discussed in a previous question. 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:

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

... and one subtable for each product type:

item_magazine
=============

item_magazine_id INT
title VARCHAR
product_id INT -> 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 -> 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'

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:

fee
===

fee_id INT
fee_description VARCHAR
item_magazine_id INT -> Foreign key to item_magazine.item_magazine_id
item_web_site_id INT -> 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

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.

However, my intuition suggests that it would be cleaner to get rid of the item_WHATEVER_id columns and keep a separate table:

fee_to_item
===========

fee_id INT -> Foreign key to fee.fee_id
product_id INT -> Foreign key to product.product_id
item_id INT -> ???

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?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about innodb