Foreign Keys to Multiple Tables

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 Received on Tue Oct 29 1996 - 00:00:00 CET