Re: Table design problem

"Roy Hann" <rhann_at_globalnet.co.uk> wrote in message news:<b6962v$bf6$1_at_sparta.btinternet.com>...
> I think I want to create a couple of subtype entities in an SQL database.
> In this database customers may be either of two subtypes: Individuals or
> Corporations. (Furthermore, Corporations can have another subtype, namely
> Non-profits, but let's ignore those for the moment.)
>
> I have no problem setting up RI constraints on the subtype tables to ensure
> that there is a corresponding customer entity in the Customers super-table,
> but I am having trouble devising a way to ensure each entity in the
> Customers table ends up with exactly one corresponding entity in either the
> Individuals table or the Corporations table, but not both. How do people
> normally handle this? I seem to need some kind of mutual exclusion
> constraint between the subtypes.

One solution is to keep all the subtypes with the supertype in one table. You can then provide views for each subtype to select only the columns relevant to that subtype. A downside is that you have to "roll your own" check constraints for columns that should be NOT NULL but don't apply to all subtypes.

With separate tables for the subtypes, what you really want is a check constraint that can look at more than one table - I believe these are called ASSERTIONS in the SQL standard, but they are not supported by Oracle, I don't know about other DBMSs. In the absence of those, other options are:

1. Include the subtype discriminator column in a UNIQUE constraint on the supertype, and in the foreign keys on the subtypes. Then add check constraints on the subtypes e.g. on Individuals: CHECK (subtype_code='IND'). Not very elegant, true!
2. Triggers.