Re: Table design problem

On Tue, 8 Apr 2003 15:07:24 +0000 (UTC), "Roy Hann" <rhann_at_globalnet.co.uk> wrote:

>> >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.
>>
>> If individuals and corporations are really subtypes, then you should
>> be looking at the supertype.
>>
>> Let's say the supertype "Parties" consists of the subtypes
>> "Individuals" and "Organizations". Since both individuals and
>> organizations can be customers, you'd associate customers with the
>> supertype "Parties".
>
>Possibly I am missing your point,

The points are that

1. "Individual" describes a kind of party.
2. "Organization" describes a kind of party.
3. "Customer" describes a relationship between two parties.

Individuals and organizations (that is, parties) exist regardless of whether they're customers. So instead of thinking "customers may be either of two subtypes: Individuals or Corporations", think "parties may be either of two subtypes--individuals or organizations", and "any party may be a customer".

>but this still leaves me with my original
>question (to which I have already received the answer I was afraid I'd get):
>how can I ensure that a party is not simultaneously an individial and an
>organization? (i.e. how can I ensure no one ever stores a row in the
>individuals table with the same PK as a row in the organizations table?)

You got an answer to that, right?

-- 
Mike Sherrill
Information Management Systems