How to represent category, subcategory, product

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 - 13:39:06 CST