Question: Link a container to more than one table
Preferences: One record
Contacts::k_ID_Record = Documents::kf_ID_Record AND
Contacts::k_1_Contacts = Documents::kf_Type
Inventory::k_ID_Record = Documents::kf_ID_Record AND
Inventory::k_3_Inventory = Documents::kf_Type
Where k_ID_Record fields are auto-serial unique IDs and k_1_Contacts, etc fields are global calculations (i.e. k_1_Contacts = 1, k_3_Inventory = 3, etc. ) *The global calculation fields are used in the database to distinguish the "type" of document.
Some documents need to be linked to both Contacts and Inventory, but some Contacts and Inventory items will obviously have identical k_ID_Record's (i.e. the 1st contact created and the 1st inventory item created).
Document::Container = DocA
Document::kf_ID_Record = 1<p>2
Document::kf_Type = 1<p>3
Instead of DocA being linked to Contact #1 and Inventory #2, DocA will be linked to Contact #1, Contact #2, Inventory #1, and Inventory #2.
The current solution I have come up with is to separate the kf_ID_Record field into separate fields:
Inventory::k_ID_Record = Documents::kf_Inventory
Contacts::k_ID_Record = Documents::kf_Contacts
Solution works, but may not be ideal in the future if I need to relate Documents to several tables. Is there a better solution to this?