How to represent category, subcategory, product
Date: 21 Nov 2006 11:39:06 -0800
Message-ID: <1164137946.016795.314600_at_k70g2000cwa.googlegroups.com>
I don't know if this counts as database theory or not so feel free to tell me this is an inappropriate group for this post but anyway: can anyone please offer any advice as to what would be the right pattern (is pattern the right word?) to model the following scenario in a relational database?
A category contains has many subcategories, a subcategory belongs to one category. So:
CatTable: (CatID, CatName)
SubcatTable: (SubcatID, SubcatName, CatID)
So far so good. Now a product is either in a subcategory or directly in a category with no subcategorisation. Of course if it's in a subcategory, its category is implied by the subcategory's parent category.
I could use:
ProdTable: (ProdID, ProdName, CatID, SubcatID)
But this doesn't seem quite right because it allows the possibility that Prod.CatId != Prod.Subcat.CatId.
Is there a better way of representing this? I'd also be grateful if anyone knows of a good resource for finding relational representations of common scenarios like this.
Thanks! TH. Received on Tue Nov 21 2006 - 20:39:06 CET