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 |
|
fridaynoon
Starting Member
3 Posts |
 Posted - 2008-05-09 : 10:50:24
|
| I have an online store with products and categories. The requirements are: Each product can be in one or more categories; a product must be in at least one category.To accomplish the first requirement, I have a Product table, Category table, and a many-to-many Product2Category table. (Probably not relevant, but a requirement is the Category table is self-referencing to provide sub-categories.)My question is for the second requirement that each Product be in at least one category. My assumption would be to add a column to the Product table called "ParentCategoryId", which is a foriegn key of the Category table's identity column.What would be the best approach to ensure a product is in at least one category?Thank you. |
|
|
pootle_flump
1064 Posts |
Posted - 2008-05-09 : 11:38:08
|
| I wouldn't do anything too fancy and simply include this logic in your application\ data layer\ CRUD code. Run reports every so often as a data checking exercise to see if any parent products have no children. I wouldn't start storing product-category relationships in two tables. |
 |
|
|
fridaynoon
Starting Member
3 Posts |
Posted - 2008-05-09 : 19:04:07
|
| Thanks for the advice - greatly appreciated as sometimes I have a tendency to over compensate for such things. And when that happens, the code can become difficult to maintain and even more difficult to program against. |
|
|
|
|
|
|
Best approach for a required entry