SQL foreign keys with multiple tables

Question

If I have three tables:

music_genres
-----------
music_type_id
genres
[other unique fields]

minerals
--------
mineral_id
mineral
[other unique fields]

verbs
-----
verb_id
verbs
[other unique fields]

and these are populated with:

rock
jazz
funk
punk

rock
boulder
stone
shale

rock
wobble
shake
vibrate

Now let's say I was displaying a web page for each of these items, each using a separate template, and stored this information in the following table:

pages
-----
page_id
page_url
template_url
foreign_key_id

with data such as:

/page/music-genres/rock/
/music-genres-template.html
1

/page/verbs/rock/
/verb-template.html
1

/page/minerals/rock/
/mineral-template.html
1

/page/minerals/rock/images/
/mineral-images-template.html
1

The templates will know that the a foreign key relates to the specific related table, so the mineral template with know to query the minerals table for that table's other fields. However, I don't have any referential integrity. If rock is deleted from the minerals table, I can't use cascading deletes to remove rows from pages, as foreign keys aren't unique. I can think of numerous ways to get around this inelegantly:

• Emulating cascading deletes with triggers
• Storing the item type in each row of the music_types, minerals and verbs tables, and using this a an additional foreign key
• Storing the appropriate table name in the pages table
• Maintaining the integrity of the database with PHP etc.

I'm thinking that there must be a better way of either storing my data or maintaining the integrity of the database. Any thoughts?

Answer 1

I think that your problem resides here

The templates will know that the a foreign key relates to the specific related table,

That's knowledge which is not stored anywhere in the database.

I see two ways out of it:

1. Given that you are actually creating separate tables for each type of 'thing', you should have a differente column for each type of thing referencing the appropriate table in your pages table, setting all columns to null except one (this could be enforced via a constraint)

2. Have a 'master things' table with a unique id which then pages could reference, having both a column to identify the type, and a column pointing to the rest of the unique data, which would be stored in a different table.