Re: How to represent category, subcategory, product

Cat:
CatID
CatName
ParentCatID

Prod:
ProdID
ProdName
CatID

You can derive levels of subcategorization via closure on ParentCatID -> CatID (a NULL ParentCatID indicates a root "category").

TH wrote:
> 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 Wed Nov 22 2006 - 19:28:19 CET