superlibrarygirl
New member
- Local time
- Today, 00:02
- Joined
- May 10, 2013
- Messages
- 3
Hello all,
I'm creating an inventory database for an antique shop/vintage clothing store that consists of tblInventoryMaster (which houses the InventoryID and information that applies to all inventory, like pricing, date purchased/sold, description, etc.) as well as a number of sub-inventory tables, like tblFurniture, tblDresses, tblPants, etc. that house the specific attributes for each sub-inventory category. (So furniture obviously has different attributes than clothing, and dresses will be concerned with things like sleeve types, while pants wouldn't be.)
Here is the problem I'm running into: The PK in tblInventoryMaster is the InventoryID, which I initially had as a FK in the sub-inventory tables. But then I was thinking that since there should only ever be one sub-inventory record per InventoryID, then there should be a one-to-one relationship between the InventoryID in tblMasterInventory and each inventory sub-table, which means that the InventoryID would need to be the PK in all of those tables. (Microsoft's description of one-to-one relationships says that they are not frequently used but that one instance when you would use them is when you have attributes that apply only to subsets of your data, which sounds like what I am doing.) So I deleted the sub-inventory PKs and made the InventoryID the PK in all of the sub-inventory tables.
Unfortunately, while I was able to create a one-to-one relationship between tblInventoryMaster and the first inventory sub-table, after that I get an error that says "Invalid field definition 'InventoryID' in definition of index or relationship" which makes me wonder whether I can only create one one-to-one relationship off of the tblInventoryMaster's InventoryID field.
Would someone be able to clarify whether it is possible to create multiple one-to-one relationships like I am talking about or whether they should be one-to-many relationships even though they aren't actually one-to-many relationships? Is there something else that would cause this error (it happens regardless of which sub-table I try to use to create the second relationship)?
Is there a different way I should be approaching this? My thinking for having the InventoryMaster table was that it would eliminate the need to repeat fields that apply to all of the inventory while allowing me to use the sub-tables for attributes applicable only to subsets of the data. But now I have run myself in circles and am thoroughly perplexed.
Thank you in advance!
I'm creating an inventory database for an antique shop/vintage clothing store that consists of tblInventoryMaster (which houses the InventoryID and information that applies to all inventory, like pricing, date purchased/sold, description, etc.) as well as a number of sub-inventory tables, like tblFurniture, tblDresses, tblPants, etc. that house the specific attributes for each sub-inventory category. (So furniture obviously has different attributes than clothing, and dresses will be concerned with things like sleeve types, while pants wouldn't be.)
Here is the problem I'm running into: The PK in tblInventoryMaster is the InventoryID, which I initially had as a FK in the sub-inventory tables. But then I was thinking that since there should only ever be one sub-inventory record per InventoryID, then there should be a one-to-one relationship between the InventoryID in tblMasterInventory and each inventory sub-table, which means that the InventoryID would need to be the PK in all of those tables. (Microsoft's description of one-to-one relationships says that they are not frequently used but that one instance when you would use them is when you have attributes that apply only to subsets of your data, which sounds like what I am doing.) So I deleted the sub-inventory PKs and made the InventoryID the PK in all of the sub-inventory tables.
Unfortunately, while I was able to create a one-to-one relationship between tblInventoryMaster and the first inventory sub-table, after that I get an error that says "Invalid field definition 'InventoryID' in definition of index or relationship" which makes me wonder whether I can only create one one-to-one relationship off of the tblInventoryMaster's InventoryID field.
Would someone be able to clarify whether it is possible to create multiple one-to-one relationships like I am talking about or whether they should be one-to-many relationships even though they aren't actually one-to-many relationships? Is there something else that would cause this error (it happens regardless of which sub-table I try to use to create the second relationship)?
Is there a different way I should be approaching this? My thinking for having the InventoryMaster table was that it would eliminate the need to repeat fields that apply to all of the inventory while allowing me to use the sub-tables for attributes applicable only to subsets of the data. But now I have run myself in circles and am thoroughly perplexed.
Thank you in advance!
