Hey all,

I am currently creating an asset tracking database for a degree course and have hit a brick wall in regards to setting up a single Foreign key field BUT with multiple references. Below are my tables:

tbl_ohp_asset
Asset_ID (PK) (AUTO-INCREMENT set to start at 1000)
Asset_Type
Asset_Desc

tbl_pc_asset
Asset_ID (PK) (AUTO-INCREMENT set to start at 2000)
Asset_Type
Asset_Desc

tbl_rtr_asset
Asset_ID (PK) (AUTO-INCREMENT set to start at 3000)
Asset_Type
Asset_Desc

tbl_movements
Move_ID (PK)
Move_Date
Asset_ID (FK) <------------------ Problem point!

My aim is that when I come to creating an online form I can select, from a drop down box, an item that has been entered into the db previously. It will be an item with the Asset_ID of one of the 3 tables (ohp_asset, pc_asset & rtr_asset) which will enable me not only to select the item in its full entity BUT create a history of where the item has been, using the Asset_No as the FK reference.

From research into this I know I can make a columns' contents a FK by using the FOREIGN KEY and REFERENCES syntax BUT how do I give the column MULTIPLE references, i.e. reference the ohp_asset, pc_asset AND the rtr_asset table inclusive? This would give me a UNION of some sort.

I hope this makes sense! Any guidence or hints would be appreciated as I am self teaching SQL which is hard enough on its own!

Thank you in advance.

your problem lies in a missing supertype table

do a search for supertype/subtype

your three asset table tables should be subtype table tables, referencing a supertype table table which logs all assets

thus, the tbl_movements table table would reference the main assets table table

simple, yes?

Thank you for a VERY quick response.. Will follow the path suggested and will let you know result!!

I understand the concepts now, and agree with your suggested path BUT after researching and looking around I cannot actually see the syntax to, for eg, make pc_asset a subtype table and then movements a supertype!

AHHHHHHHHHHHHHHH! I will keep looking and hopefully it will "appear" on my screen LOL!

Many thanks again..

no, no, movement isn't the supertype

you need to declare another table, "all_assets", as the supertype table, and it is the only one which has AUTO_INCREMENT

then ohc_assets, pc_assets, and rtr_assets are subtype tables, with their PKs also asset_id, but not AUT_INCREMENT, and the same asset_id is also a FK to the all_assets table

of course, once you have declared the all_assets table, you're gonna ask which columns it should have, and the answer is, all the columns that are common to all subtypes

in fact, it's possible you do not need the subtype tables, and you can simply add a column called "asset_type" to the all_assets table

the reason for separate subtype tables is when there is a preponderance of columns that are unique to only one subtype