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.
>
> Procrustes

One way to make sure you have 0-1 subtypes for each supertype is to include type in a unique constraint in the super table, and then let the subtables reference that. This will ensure that you have at most one subtype for each supertype (Note that it is possible to end up with a supertype without a subtype)

create table super (

    id integer not null primary key,
    type integer not null,
    check (type in (1,2)),
    unique (id, type)
)

create table sub1 (

    id integer not null primary key,
    type integer not null default 1,
    check (type = 1),
    foreign key (id, type) references super (id, type) )

create table sub2 (

    id integer not null primary key,
    type integer not null default 2,
    check (type = 2),
    foreign key (id, type) references super (id, type) )

HTH
/Lennart Received on Mon Mar 31 2003 - 17:07:01 CEST