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

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.

Marshall Received on Fri Dec 19 2003 - 13:42:24 CST