June 23, 2012 at 8:07 pm
Hello all,
I am running into an issue regarding the relationships between a few of my tables. I have a 'Products' table that contains values which link to 2 other tables, 'ProductCategory' and 'ProductSubCategory.'
For example:
ProductKey | ProductName | ProductCategory | ProductSubCategory | Price
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
1 | Computer | 2 | 1 | 1000.00
2 | White Board | 1 | 1 | 1000.00
The ProductCategory and ProductSubCategory columns link as foreign keys to the respective primary keys in their respective tables. The ProductCategory table has 2 columns (ProductCategoryKey and ProductCategoryName) and the ProductSubCategory table has 2 columns (ProductSubCategoryKey and ProductSubCategoryName). The issue is that I would like to add a column to the ProductSubCategory table that links it to the ProductCategory it belongs to. The problem is that, since one ProductSubCategory can belong to multiple ProductCategory keys, I cannot insert records in the ProductSubCategory table because it would create a record that reuses the ProductSubCategoryKey. Is there anything I can do as a workaround to establish these relationships?
Thanks kindly in advance
June 25, 2012 at 7:17 am
It seems that there's something wrong with your design.
You say subcategory can be part of multiple categories.
However, in your product table you have a FK to subcategory and a FK to category. That doesn't seem right. In the product table you are limiting the subcategory to one category only by explicitly including that FK in the table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply