Table row referencing one table or another

Question

I've got one table containing some sort of items, and two tables (a and b) to which these items belong to.

One item refers to either one row in a or one row in b. One a or b can have multiple items.

Is there a better design than the following (using Oracle 10)?

Any pitfalls to watch out for?

id   a_id(fk)    b_id(fk)
 1          1        NULL
 2          1        NULL
 3       NULL           1
 4          2        NULL

Answer 1

A and B are disjoint subtypes of a more general type.

Let's call it refs:

CREATE TABLE refs (type CHAR(1) NOT NULL, id INT NOT NULL, PRIMARY KEY (type, id), CHECK (type IN ('A', 'B')))

CREATE TABLE a (type CHAR(1) NOT NULL, id INT NOT NULL PRIMARY KEY, FOREIGN KEY (type, id) REFERENCES refs (type, id) ON DELETE CASCADE, CHECK (type = 'A'))

CREATE TABLE b (type CHAR(1) NOT NULL, id INT NOT NULL PRIMARY KEY, FOREIGN KEY (type, id) REFERENCES refs (type, id) ON DELETE CASCADE, CHECK (type = 'B'))

CREATE TABLE items (id INT NOT NULL, type CHAR(1) NOT NULL, ref INT NOT NULL, FOREIGN KEY (type, id) REFERENCES refs)

With this design, you should never delete from A or B directly: delete from the parent table refs instead.

Answer 2

That's a pretty good design on a number of counts:

• With the foreign key on a_id you enforce the link to table A.
• with the foreign key on b_id you enforce the link to table B.
• The 1:many relationships (between items and table A, and between items and table B) are each correctly stored.

The only snag is that this database structure by itself doesn't check that an item is linked to only one of A or B (and not both). Check constraints on the Items table will do this job.

Pseudo-code example:

CONSTRAINT a_eor_b CHECK 
  (
    NOT (a_id IS NULL AND b_id IS NULL)
    AND NOT (a_id IS NOT NULL AND b_id IS NOT NULL)
  )

Answer 3

I'd suggest to a add a check constraint that enforces at least (or possible exactly) one of the id fields being null.

Also a view and/or function based index for the value nvl(a_id, b_id) might be useful. With Oracle11 you could use a virtual column.

Answer 4

refer to Mapping Inheritance Structures, there are 4 techniques mentioned there,

• Map the entire class hierarchy to a single table
• Map each concrete class to its own table
• Map each class to its own table
• Map the classes into a generic table structure

and see "2.6 Comparing The Strategies" for pros & cons.