Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: How to represent category, subcategory, product

Re: How to represent category, subcategory, product

From: Lennart <Erik.Lennart.Jonsson_at_gmail.com>
Date: 22 Nov 2006 06:11:50 -0800
Message-ID: <1164204710.745030.40790@m73g2000cwd.googlegroups.com>

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 - 08:11:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US