Re: Foreign Keys to Multiple Tables
Date: 1996/10/31
Message-ID: <846773068.17037_at_dejanews.com>#1/1
In article <01bbc5d0$f9217710$946020cf_at_trooper>,
"Michael B. Klein" <michael_at_wpc-edi.com> wrote:
>
> I have a lot of experience with desktop database work (Clipper, Paradox,
> Foxpro, etc.), but now I'm moving into the Oracle arena, and I have a
> design question. The following is a simplified version of the situation.
> I have a group of tables describing the following hierarchy:
> Manufacturer -> Category -> Item
> Each manufacturer can contain multiple categories, and each category can
> contain multiple items. The Category table contains a Category_id,
> Parent_id (Manufacturer_id), and Sequence (along with its own descriptive
> columns). The Item table contains an Item_id, Parent_id (Category_id), and
> Sequence (and descriptive columns).
> Seems straightforward enough. However, sometimes, there is an unknown
> number of subcategories. The way I currently handle this is to have the
> Category table contain a column indicating whether the current category's
> children are Items or Categories. If a category is a sub-category of
> another category, its Parent_id points back to the Category table, not to
> the Manufacturer table.
> Is there a better way to do this? I'd like to use constraints to maintain
> referential integrity, but as far as I can tell, referencing multiple
> tables with the same column is going to throw a big wrench into that plan.
> Since I need an arbitrary number of subcategory levels, creating another
> table in between Categories and Items won't help, either.
> Anyone got a quick solution?
> Thanks,
> Michael
>
The ParentId in the Category table should contain only Manufacturer IDs. I would add another column to the Category table called CategoryParent. If a category is a sub-category, place the key to the parent category in this column, otherwise leave it null.
This article was posted to Usenet via the Posting Service at Deja News: http://www.dejanews.com/ [Search, Post, and Read Usenet News!] Received on Thu Oct 31 1996 - 00:00:00 CET