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 - 2003-12-14 : 16:35:32
|
| Hi there,I have a table of products and each product has a field which references a category ID.I want to give my products the option to belong to multiple categories so I figure I can add a table of secondary categories and have each record reference a product and a category.In addition, I would like to fix it so that a category ID cannot be specified twice for a product. Its simple enough to add a unique constraint to the secondary categories table but how do I stop the primary category being referenced inline in the actual product record from appearing in the secondary category table for that same product?I figure I could move the primary category out of the main product record and into the SC table but then how do I enforce the requirement that every product is of at least one category?Any thoughts,XF. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-12-14 : 17:42:15
|
| I'd get rid of the category id in the table of products and rely on a secondary tableCREATE TABLE dbo.ProductCategory (ProductID INT NOT NULL ,CategoryID INT NOT NULL)CREATE UNIQUE CLUSTERED INDEX IDX_ProductCategory ON dbo.ProductCategory (ProductID, CategoryID) |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2003-12-14 : 19:05:39
|
| Thanks for your reply.That's cool, but how do I force a product to have at least one reference in the new table? To get the database to do this I think would require a trigger.Also, how do I mark a record in the new table as a primary category? I think maybe a bit field would suffice and then maybe another unique constraint on the prodID and the bit field?Any thoughts? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-14 : 19:27:54
|
| One way is to add a column called "PrimaryCategory" to your products table, and make it required.Then use a table similiar to what Sam posted but call it "AdditionalCategories" or something like that. In that table, you save lists of the the non-primary categories for each product.To get ALL of a product's categories, then, use a union:SELECT Product, PrimaryCategory as CategoryFROM ProductsUNION ALLSELECT Product, CategoryFROM AdditionalCategoriessomething like that, anyway ... though you might like to make sure that a product's primary category isn't also listed in the additional categories table...- Jeff |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-12-14 : 20:13:38
|
quote:
Originally posted by X-Factor
That's cool, but how do I force a product to have at least one reference in the new table? To get the database to do this I think would require a trigger.
Depends, on how the code is written to do the insert. Write code to refuse the insert if there's no primary catagory ?IF @Catagory IS NOT NULL BEGIN INSERT INTO dbo.MyTable ( , , , ) ... etcEND ELSE BEGIN -- Error handling hereENDAre you writing stored procedures to do the INSERTs and SELECTs ? |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2003-12-15 : 08:46:10
|
Yes I am writing stored procedures. quote:
you might like to make sure that a product's primary category isn't also listed in the additional categories table
What I would like to know is whether there is a way of doing this with some standard database mechanism.For example, if you want unique values in a field, you add a unique constraint to the field. You might also do checks on data before even sending it to the database, but once its there, the database is ultimately going to rely on its self to maintain the integrity of the data it holds.Similarly, I could quite easily write some code to make sure that the product's primary category is not also in the additional categories table, but I would like to know whether there is a standard or intrinsic way of enforcing this using internal databse features.Hope this makes sense! |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-12-15 : 09:26:27
|
| I get your drift, but I think the answer in this case may be no, unless a trigger is used as a final resort to confirm the CategoryID exists for each product in the ProductCategoryID table.One possability is that the Product table doesn't hold a value of the Primary CategoryID but instead keeps a pointer to the row in the ProductCategory table which describes the Primary Category. All Categorys in one table, primary is described and constrained.The design goal of having a primary key kept in one table and the secondary keys in another could be a subject of some db design criticism, but it could be the proper design, depending on how the data will be retrieved and manipulated later on.I like Jeff's idea of using a primary category flag in the category table because I can see future query writing simplified by this flag.For example: select a recordset of all catagories for a product, including the primary catagory (perhaps as the first row)....SELECT ProductName, CategoryIDFROM Products PINNER JOIN ProductCategory PC ON PC.ProductID = P.ProductIDORDER BY ProductName, PC.Primary, CategoryIDThis is about as simple as it can get to return a recordset ordered by product, with the primary category listed first, and the secondary category in alphabetic order.If the Primary Category is kept in the Product table, the equivalent SELECT statement isn't as clear and would look something like this:SELECT ProductName, CategoryIDFROM (SELECT Productname, CategoryID, 1 as CatOrder -- The 1 is for positioning in the final recordsetFROM Products PUNION ALLSelect Productname, PC.CategoryID, 2FROM Products PINNER JOIN ProductCategory PC ON PC.ProductID = P.ProductID)ORDER BY ProductCategory, CatOrder, CategoryIDSo there could be a big difference in the difficulty of building recordsets depending on the chosen table structure. |
|
|
|
|
|
|
|
|
Multiple Categories