Re: How to represent category, subcategory, product

TH wrote:
> Wow, what a great response! I waited days with no reply on a SQL Server
> group, I should have come here first.
>
> Thanks Gene, Lennart, Neo, Bob for your suggestions, some very helpful
> ideas there. As Bob suggests, this was something of a reduction of my
> current requirements so I'll have to consider how the various options
> sit with the rest of the design, but I can see how each of approaches
> would be appropriate in situations I've come across.
>
> I think Lennart's method fits the best in this case. I must admit I
> didn't realise it was possible to have a two-field foreign key.
> Although for some reason which I can't fathom, this method appears to
> allow a Product to reference a non-existent Category or Subcategory as
> long at the second part of the key is a null. I don't know if it's
> possible to tighten up this aspect?
>

Add another fk pointing to category.

create table ProdTable (

   ProdID ...,
   ProdName ...,
   CatID...,
   SubcatID...,
   foreign key (SubcatID, CatID)

      references SubcatTable (SubcatID, CatID),    foreign key (CatID)

      references catTable
)

now if subcatid is null, catid must still exist

/Lennart Received on Wed Nov 22 2006 - 15:11:50 CET