Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2009-03-18 : 01:19:51
|
| Hi there,If I have tables like this...tbl_product_in_cart===================ProductID int PKCartID int PKQuantity inttbl_product_in_cart_attribute=============================ProductID int FKCartID int FKAttributeValue intSo the PK of the 'tbl_product_in_cart' table is a combination of two fields, ProductID and CartID. Thus if a user tries to INSERT the same product into their cart twice then the app will throw a duplicate PK exception. (Of course what should be being done is that the first instance of the product should have its Quantity field incremented.)Now, lets say that one type of product has an attribute and that the value for this attribute is stored in a different table. Its in a different table because not all products need a value for the attribute and so if there was a field for the attribute's value in 'tbl_product_in_cart' then this table would not be in first normal form.For the product which requires the attribute, a user should be able to add to their cart multiple instances of this product which differ in the value of the attribute.What this means is that I can have multiple rows in 'tbl_product_in_cart' where the CartID and the ProductID are the same because the value of the attribute differentiates the two instances. So, in effect, the product that requires the attribute value is having its primary key extended across another table.Now, as far as I know this is not possible so for my app I've just de-normalized the two tables together and products that don't need an attribute value just get a default value for the attribute field.Thus the question is, is it possible to maintain first normal form and still be able to spread the uniqueness I'm after across multiple tables?Cheers, XF. |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-03-20 : 06:44:37
|
| Ooo, I think you can get uniqueness across tables with a unique index on a view. |
 |
|
|
|
|
|
Extend Primary Key