Re: Table design problem

"Mike Sherrill" <MSherrill_at_compuserve.com> wrote in message news:oka39vkqh45kago16ttprditom7bt1181i_at_4ax.com...
> On Mon, 31 Mar 2003 10:42:39 +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, 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?)

Incidentally, the answers I got all suggested adding a discriminator to the value of the PK, and constraints in the subtype tables to ensure that only a PK value with the appropriate discriminator gets stored. Ugly and obscure and probably not relational, but it achieves the short-term objective.

Roy Received on Tue Apr 08 2003 - 17:07:24 CEST