Re: Relation problem

Ok, I think I got it.

When you create an individual, you add a row in the Party table with the PK of Individual. When you create an Org, you add a row to the party table with the Org PK. Then you link the Party table to addressLink table. So you have a 1:1 relationship between the Ind/Org and party table, then a 1:m between the party and addressLink table.

So the performance hit on this design is with adding a new Ind/Org. After the initial population of the DB, these would be added 1 at a time so the extra time taken is Ok, but queries to match org/ind with addresses or vice versa, matching given address ranges with people would be faster.

Is this logic correct?

Michel

Heinz Huber <Heinz.Huber_at_elbanet.co.at> wrote in message news: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 - 17:33:03 CET