Re: How to represent category, subcategory, product
Date: 22 Nov 2006 06:11:50 -0800
> 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 (
foreign key (SubcatID, CatID)
references SubcatTable (SubcatID, CatID), foreign key (CatID)
now if subcatid is null, catid must still exist
/Lennart Received on Wed Nov 22 2006 - 15:11:50 CET