Foreign key pointing to different tables

Question

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?

Update

The alternative I was actually considering is:

fee
===

fee_id INT
fee_description VARCHAR
product_id INT -> Foreign key to product.product_id
item_id INT -> ???
net_price DECIMAL

I'm not sure why mentioned a separate fee_to_item table (I guess I was thinking of something else) but it doesn't really change the question since the key point is the same: foo1_id+foo2_id+foo3_id vs source_id+foo_id

Answer 1

Some databases do not even allow FK columns to have nulls in them, which would prevent your first solution. I cannot recall if MySQL does or does not. But I agree that a separate table is better. If need be, you can UNION them together so people that want to see all fees together can do so. But since there are fees for different 'whatevers' today, it is also possible that they will vary in the future, and then you might be forced to split the table because a type of fee only applies to one. For example, perhaps your fees would be quantity based in the future, and there is no such thing as a quantity for a web-site. Or time based, or coupon based, or whatever.

In addition, if you add a third type of thing on which you charge fees, do you add it to the same table? Or do you split it at that point? I think you are forcing these together because they are similar, but they are not really the same thing.

At any rate, I say split the tables.

Answer 2

I usually go with one FK column and a flag column. So instead of

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 

you'd have

fee 
=== 

fee_id INT 
fee_description VARCHAR 
item_id INT -> Foreign key to item_magazine/website.item_magazine/website_id 
product_type_id INT -> Foreign key to product_type.product_type_id
net_price DECIMAL 

Then your queries are generalizable. Instead of 2 different queries, e.g.:

SELECT * FROM fee WHERE item_magazine_id=x
SELECT * FROM fee WHERE item_website_id=y

You do something like:

SELECT * FROM fee WHERE item_id=x and product_type_id=1
SELECT * FROM fee WHERE item_id=y and product_type_id=2