How to ensure only one of two sets of optional information?
Date: Fri, 19 Dec 2003 19:42:24 GMT
Message-ID: <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.
Marshall Received on Fri Dec 19 2003 - 20:42:24 CET