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:
Asset_ID (PK) (AUTO-INCREMENT set to start at 1000)
Asset_ID (PK) (AUTO-INCREMENT set to start at 2000)
Asset_ID (PK) (AUTO-INCREMENT set to start at 3000)
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!
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!