Relation problem

From: Michel <microworld_at_sympatico.ca>
Date: Thu, 18 Jan 2001 01:46:24 GMT
Message-ID: <QVr96.116873$f36.4911387@news20.bellglobal.com>

Designing this large database where we store adresses as their own entity. i.e. an address exist by itself.
We also have individuals AND organizations that can have have more than 1 address. So far so good, created a link table were I wanted to use the primary key as individualID and AddressID.

This would be great, but it's got to be an individual OR an organization, so I cant have referencial integrity enforced at the database level.

Individuals Organizations Addresses AddressLinks

AddressesFK

IndividualFK

OrganizationsFK

So I would have to create a addresslinkID and have either an OrgID or IndividualID. I don't like this especially since they will be about 8M addresses and lots of searches and lookups.

I thought about created a union table with all the ID from Individuals and Orgs and then having a addressLinks use the uniqueID from this table.

Uniontable                    AddressLinks
UnionIDPK                    UnionIDPK
IndividualsFK                  AddressesIDPK

OrganiztionsFK

But it still requires the uniontable to have 2 relationships with the other tables that is not primary.

Any feedback on this would be very helpful.

Michel Received on Wed Jan 17 2001 - 19:46:24 CST