Re: How to represent category, subcategory, product
Date: 22 Nov 2006 10:28:19 -0800
You can derive levels of subcategorization via closure on ParentCatID -> CatID (a NULL ParentCatID indicates a root "category").
> 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
> 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 Wed Nov 22 2006 - 19:28:19 CET