This would be a lot easier to understand if we knew what the tables and fields meant. I am pretty sure I know what an image is, but what does a record in the Inventory table represent? "One image has many inventories" just doesn't do it for me.
And what is the difference between "Inventory ID" and “Inventory Serial Number”? And what exactly is inventory type and why does it need to be duplicated in both tables?
Thanks for the quick reply; I'll try and clarify.
A record in the inventory table represents one item in inventory, it's manufacturers serial number (like a car's VIN), a "type" description and some other information about it. Many items in inventory, many types of inventory items. In the "images" table, instead of having one image per each serial number, I have a general list of images relevent to the type of item in inventory...an "images" record is created to represent an image of the "inventory type". Inventory type can be described as a set of inventory, like a model of a car; or airplane eg 737, or 757
Inventory ID is auto generated and represents the DB global ID since it's the main table (out of 30+) that everything else relates to. Inventory "Serial Number" is a industry type serial number that makes more sense to people reviewing the database; it's really the Manufacturers SN.
As to duplicating the "Inventory Type" in both? I expect its required in "Inventory since I pull it into multiple downsteam OTM links. As to it's need in "images" I thought it was needed to identify the "type" when someone was inputing a new "type" of inventory in "Images".
OK, then - suppose we have two tables:
• ItemID (auto-entered serial number)
• CategoryID (auto-entered serial number)
Categories:: CategoryID = Items:: CategoryID
Note that this assumes an item can belong to (at most) ONE category.
Now, what exactly is your question?
Working through this you've helped me recognize where the duplicity is. I was trying to force two fields: "Type" together rather than allowing the foreign ID to help collect Type from the other table. Made a value list on the foreign ID and it works well. My hangup was twofold: a) tied to the main Table and letting go of "type" as defined there, since most of the entry happens there I felt obliged (wrongly) to hold that field sacred. b) I was reluctant to tweak on the foreign ID.
Now I need to make sure that other links several tables deep that refered to "Type aren't hosed "... but for now, I'll close the item. Thanks again.