Re: Relation problem

From: Heinz Huber <Heinz.Huber_at_elbanet.co.at>
Date: Wed, 31 Jan 2001 07:51:11 +0100
Message-ID: <3A77B5DF.C28803DA_at_elbanet.co.at>

Hit reply to sender before, so now the whole thing:

Michel wrote:
>
> Heinz,
>
> > Individuals and Organizations can have more than 1 address.
>
> The other part of the problem is individuals and organizations can have more
> than one address, so this makes so I cannot put the PK of addresses in these
> tables.

I missed that one :-(

> I'm starting to go towards the idea of having 2 addresslink tables, one for
> individuals and their addresses, and one for organizations and their
> addresses.

Since an address doesn't have to have an individual or an organization at all, this setup looks OK to me.

The only question that remains is: How often do you go from addresses to individuals or organizations?
If the answer comes close to never, it is only important to link the individual or respectively the organization table to addresses. This is best done by the design you propose above. On the other hand, if you have to go from addresses to individuals or organizations often, the party table design would probably better. Just to make sure, we're talking about the same thing a short sketch of the relations (same layout as Mike's):

Parties

  PartyID       PK
  Type          Ind/Org

Individuals option 1
  PartyID       PK, FK

  IndiviualID optional (perhaps PartyID can be used), unique, candidate key
  ...

Organizations option 1

  PartyID       PK, FK
  OrgID         optional (perhaps PartyID can be used), unique,

candidate key
  ...

Individuals option 2
  IndiviualID PK
  PartyID FK, not null
  ...

Organizations option 2

  OrgID         PK
  PartyID       FK, not null

  ...

Addresses
  AddressID PK
  ...

AddressLinks

  PartyID       PK, FK
  AddressID     PK, FK

  other optional attributes (like movedToDate, ...)

Going from an existing design to this one, you simply would have to replace the PK of Individuals and Organizations for option 1 or add PartyID for option 2.
Performance should be OK, since you're always joining / selecting on PKs/FKs.

hth,
Heinz

> -----Original Message-----
> From: Heinz Huber [mailto:Heinz.Huber_at_elbanet.co.at]
> Sent: Tuesday, January 30, 2001 3:04 AM
> To: Michel
> Subject: Re: Relation problem
>
> Michel wrote:
> >
> > Ok, Sorry I missed my own tread! I'm not used to actually getting
 feedback
> > lately on my questions. Thank you very much for the enlightment, I feel
> > like I'm back at school.
> >
> > First clarification:
> > The primary problem is performance between addresses and (Organizations OR
> > Individuals).
> > Individuals and Organizations can have more than 1 address.
> > An address can exist by itself.
> >
> > So, there will be addresses in the address table that are not assigned to
> > anybody, but Individuals or Organization do require and Address.
>
> I think that Jan and MSherrill (?) were going the other way: There may
> be individuals or organizations without address but never the other way
> round.
>
> The way you present it now, it sounds like: An individual or
> organization must have exactly one address. Addresses may or may not be
> related to any number of individuals or organizations.
> If I'm right on the above, the solution should be quite simple:
> Include the PK of the address table in the tables for individuals and
> organizations as a foreign key and make it a not null field (required!).
Received on Wed Jan 31 2001 - 07:51:11 CET