I am creating a database for packing for a trip. I have a table with items I am going to pack. I have Suitcase/Bag into which the item will be going. I would like to document which compartment of the Suitcase/bag the item will be going. For example - Items (sock) will go into the Suitcase in the Compartment (Main).
Another Suitcase/bag is a Backpack; this has a Compartment (Computer Compartment).
Currently I have three tables - Items, Suitcase, Compartment.
If I put the socks in the Suitcase, I would like to also be able to place it in ONLY a compartment in the Suitcase. How do I write this so I can only link with a compartment in the suitcase if the item is linked to the suitcase?
I had considered placing separate entries in the in the Suitcase table, i.e. Suitcase/Bag Table - First Entry (First column Suitcase Second column Main Compartment.) Second entry (First column Backpack Second Column Computer Compartment) etc.