Re: How to ensure only one of two sets of optional information?

"Marshall Spight" <mspight_at_dnai.com> wrote in message news:AwIEb.88899$8y1.292404_at_attbi_s52...
> Hi all,
>
> Let's say I have some data and there are some attributes that
> are common, and some that are optional in one of two ways.
> For example, say I have an Accounts table with some common
> attributes, but there are two subtypes of Account: Receivable
> and Payable.
>
> I could use this schema:
>
> Accounts (
> id int primary key,
> ... )
>
> AcountsPayable (
> id int primary key foreign key references Accounts(id),
> ... )
>
> AcountsReceivable (
> id int primary key foreign key references Accounts(id),
> ... )
>
> The Accounts table has attributes that are common to
> all accounts; the Payable table has attributes that only
> apply to payable accounts, the Receivable table has
> attributes that only apply to accounts receivable.
>
> With this structure I have guaranteed that each account
> row will have at most one receivable or payable row,
> but I haven't done anything to ensure mutual exclusion.
> That is, I want to ensure that each account id has
> *either* a row in payable or a row in receivable, but
> not both.
>
> I could do it with a database constraint. Is there a structural
> way to do it that I'm not seeing? If I have to use a constraint,
> is there a canonical form for this particular example? It seems
> like a common thing to want to do, but I haven't come across
> how to address the subtype-data-exclusion issue anywhere.

Make account type part of the account candidate key and include a constant attribute in the referencing relations. Received on Fri Dec 19 2003 - 21:27:07 CET