I need to link address records to the people who use those address records. An address can be used by an employee, a client company, a contact at the client company, etc. More than one person can use a given address. For example, you could have an address for a given client company and associate that same address record with a person who works at that company. Also, a given person/client/whatever can have multiple addresses (i.e. home address, business address, whatever). Thus I need a convenient way to link my Employee Table, my Client Table, my Contact table, and a few other tables to my Address table.
I tried creating multiple XREF tables, for example
with primary key a composite of
where FK_Address is a foreign key to an address record and FK_Employee is a foreign key to an employee record. (There are a few other fields in the record as well, such as whether this is a home address, a mailing address, a business address, or whatever).
There was a similar table for XREF_Address_Client, XREF_Address_Contact, etc.
Although it works just fine, I find myself having to write multiple almost-identical procedures for each sort of address operation, one for employees, one for clients, etc. I rethought it and am considering going to a single XREF_Address_To_Owner table with a primary key consisting of
(The owner type identifies whether it is an employee, client, or whatever. The OwnerID points to the specific record in the corresponding table. This is not a foreign key per se because it does not point to a specific table.)
This eliminates all of the redundant stored procedures and simplifies quite a few other things as well
My questions are these:
First of all, what sort of a performance hit (if any) might I take because of the fact that OwnerID is not a true foreign key. It points to the ID field of another table, which is the primary key of that table.
Second, I frequently need to gather all addresses for a given owner, which means that I do a select where "OwnerID = @OwnerID and OwnerType = @OwnerType". Since these two fields are part of my primary key, will the search be optimized or should I create a separate index on the OwnerID/OwnerType combination?