Re: Relation problem

I think, you nearly have it.

Michel wrote:
>
> 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.

Not quite. I would do it the other way round. When creating an individual or organization, also create a party and put it's PK into the individual/organization table. That way you have a simple PK in the party table. Otherwise you would have to include the type in the PK.

As I wrote below, you have two options of putting the party PK into the ind/org tables: As a PK (it's always unique) or as a simply FK not null. In both cases, you should probably add a constraint connecting the type to the linked table.

> 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.

Actually the relationship between ind/org tables and party table is 0..1:1. You're correct about addressLink.

> 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.

The design with a party table only MAY improve performance when you go from addresses to ind/org. If you only go from ind/org to address, the simplest (and most performing) way would be to put the address PK into the ind/org tables.

Actually on rethinking it, you should try putting the address PK into the ind/org tables first. If that doesn't give you the performance you want, you can consider the design using a party table.

> Is this logic correct?

Pretty close ;-)

Regards,
Heinz

> 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 Thu Feb 01 2001 - 09:07:55 CET