Relation problem

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

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 Thu Jan 18 2001 - 02:46:24 CET