Re: Foreign Keys to Multiple Tables

Michael B. Klein 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

In the Category table I would add a column Parent_category_id (or something you find meaningful) and then have two foreign keys on the category table. One the manufacturer_id referencing the manufacturer_id in the Maufacturer table. Second on the Parent_category_id referencing the category_id in the category table (for the recursive relationship).

In the category table make both the manufacturer_id and parent_category_id columns nullable. Then add a check constraint that both of these columns cannot be null and one must be null.

The check constraint will ensure that each row has at least one foreign key reference. The column with a null value will pass the foreign key constraint and the column without the null will enforce there is a record in the parent table.

We have used this a number of times to enforce foreign key references to multiple tables.

Good Luck.

Dan McEvoy
Hatch Associates
Mississauga, Ontario, Canada Received on Wed Nov 13 1996 - 00:00:00 CET