How do you model an exclusive OR relationship in a super/sub type entity relationship diagram? For example, a super type entity "Product" has three sub Types: "Meat", "Bread", and "Fruit". Product is keyed by ProductID. Meat is keyed by MeatID, Bread by BreadID. A ProductID is either Meat or Bread or Fruit.
One alternative (used by Silverrun) is to overload the subtype foreign key and add a flag attribute to Product that tells which subtype it is. The benefit is to this is the subtype foreign key is not null, but the attribute flag has weaknesses.
Another alternative is to have a collection of nullable foreign keys. Each subtype would have a FK on Product. What other alternative designs exist and what approach do you recommend?
The way that I would model this would depend a great deal on the needs of the application and the development team that was using the database. Some tools don't cope well with decomposed element design (which would be my first choice), others don't cope well with left joins, some are just plain grumpy without any obvious reason!
My first choice (if the tools and the team weren't issues) would be to use the decomposed element design. Create a product table to house all the columns that were shared by all of the products: surrogate key, SKU, weight, unit type, cost per unit, etc. Then I'd build separate tables to contain the columns that were specific to a particular product type (bread, fruit, meat, etc) as dependent tables: they would each have the product's surrogate key as a foreign key that was also their primary key.
Note that this doesn't strictly meet your "exclusive or" constraint. A product could have matching rows in two or more sub-types. I don't know of any declarative way to prevent this, and in the past I've been bitten by attempts to prevent it (think of multiple inheritance).
The "logical high ground" pretty well dictates a decomposed solution. The tools and people working on the project might demand (or at least be much more practical) using another solution.
For More Information
- Dozens more answers to tough database design questions from Pat Phelan
- The Best Database Design Web Links: tips, tutorials, scripts, and more
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.