SQL Server Performance Forum – Threads Archive

table structure, one group-many categories

Gurus Table Products
Id – ProdName – GroupID – CatID
1 – Sink Cleaner – 1 – 1
2 – Sunscreen- 1- 2
3 – Disposable Nappies – 1 – 4
4 – Bath Cleaner 2 – 4 Table Groups
GroupID – GroupName
1 – Miessence
2 – Sole Natural
3 – Nahtii Table Categories
CatID – CatName
1 – Kitchen
2 – Personal Care
3 – Bathroom
4 – Nappies Product belongs to a Group (being the manufacturer), The manufacturer has products in more than one category. My question to my guru friends is this… is this the correct way to structure this? (Putting a CatID in the Products Table) The relationships seem to be a bit unusual as the Product belongs to only ONE group, but the Group may have Products in more than one Category. And the Product doesn’t really have a direct relationship with Categories. The user needs to browse products either by Category or Group. (Its an online shopping cart.) Gratefully yours
"…as a thief in the night…"

---

If you have only one column in group and category then it is better to go for denormalisation.
For me it makes no sense in keeping description of category and group in diffrent tables as they both have only one column.

---

Hi ya, the questions that you need to think about first are
is there only ever one group for a single product?
is there only ever one category for a single product?
I’m assuming that a single group has multiple products and a single category has multiple products and that groups and categories don’t have any relationship between them if the answer is yes to both of those then the design you have looks right, if the answer is no to either of them then for the one that you’ve answered no to you’d need an intermediary table to resolve the many-many relationship. e.g. if a single product can belong to multiple categories then you’d need a table with catid, pruductid, and not have catid in the product table. if the answer is no to both questions then you could possibly use a subtype relationship as below Table Products
ProdID – ProdName
1 – Sink Cleaner
2 – Sunscreen
3 – Disposable Nappies
4 – Bath Cleaner Table prod_taxonomy
ProdID, TaxID, TaxCatID
1 – 1 – 1
1 – 2 – 1
2 – 1 – 2
2 – 2 – 1
3 – 1 – 4
3 – 2 – 1
4 – 1 – 4
4 – 2 – 2 Table product_taxonomy
TaxID – TaxName
1 – Categories
2 – Groups Table taxonomy_category
TaxID – TaxCatID – TaxCatName
1 – 1 – Kitchen
1 – 2 – Personal Care
1 – 3 – Bathroom
1 – 4 – Nappies
2 – 1 – Miessence
2 – 2 – Sole Natural
2 – 3 – Nahtii There are loads of ways to skin this cat. First are questions about relationships and whether they are many-many, one-many, many-one or one-one. Once you have this then for those entities which have the same relationship with more than one other entity you can sometimes use subtypes to generalise the relationship. In the example above it is very easy to add extra taxonomies if you want a different way of presenting the same products Cheers
Twan

---

ranjitjain
thanks for your help. valid point! regards
richgran "…as a thief in the night…"

---

twan
thats it… i had it stuck i my mind that groups had to belong to a category… where as i think its the products that belong to a catergory.

excellent! your help has helped! (<–that sentence works). you guys are great! richgran
"…as a thief in the night…"

---

]]>