Solved

Searching on part of a composite key

Posted on 2011-03-23
5
326 Views
Last Modified: 2012-05-11
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

XREF_Address_Employee

with primary key a composite of

FK_Address
FK_Employee

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

FK_Address
OwnerID
OwnerType

(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?

Thanks.
0
Comment
Question by:TimAttaway
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:jogos
Comment Utility
Performance is no easy answer because that includes all usage off that table.  So one query can gain speed, another will loose.
Less tables/indexes -> maybe easier found in cache
Full table scan on combined table -> loose a lot if only a type is used that has a type that represents a small % of records
When type becomes a part of your primary key, when used in other table it makes other tables larger because the FK also has 2 columns.  

Combining in one table will sertainly need closer followup on how it is used during whole lifetiime.

0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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.

The second sentence in the above implies, to me, that you have separate Client_Address, Employee_Address, and Contact_Address tables.  So, now that you have reconsidered teh cross-reference table issue and consolidated that into one table, why not reconsider the Address table.  For instance, with multiple tables you still have the potential for having the same data in multiple tables and, thus, a potential for maintenance issues.  Suppose you have a Client who is also a Contact and the address is the same for both of those "existences".  That means that you would have a Client_Addresses table entry that is exactly like a Contact_Addresses table entry.

 Now, unless you have all manner of procedures and safe-guards in place to prevent it, it is entirely possible that, when the Client sends in a change of address, the Contact's address stays the same.  On the other hand, if you if you had a single PersonAddress_XRef table and a single CompanyAddress_XRef table, when the Client sends in that change of address and someone updates the Client's address, the Contact's address will also be updated because they both point to the same row in the same table.  

As a matter of fact, if you carry this through one more step, you might have a Persons table that has the information about any and every "Person" with whom your system deals.  That would let you have a Companie table (which might have several Addresses associated in the CompanyAddress_XRef table with a codes (e.g. S for "Shipping Addres", M for "Mailing Address", etc.?) and a set of rows in the Company_Persons table which links the the CompanyID and a PersonID (which, in turn, lets you link to the Person's PersonAddress_XRef via the PersonID (your former "OwnerID"), AddressID, and PersonRelationshipType (your former "OwnerType").  

This arrangement further normalizes the overall structure of the data and provides flexibility for having multiple addresses for both the Companies and Persons.
0
 

Author Comment

by:TimAttaway
Comment Utility
Thanks for the comment, but I think perhaps you misunderstood the situation.

There is a single Address Table.

There is an Employee Table

There is a Client Table (describing client companies)

There is a Contact Table (people who do not work here but are contacts of some sort.  They may or may not be employees of the client companies)

There are a few other tables as well that play into this, but this is enough for discussion.

An Employee, a Client, or a Contact can have zero, one, or multiple addresses on file.  A given address record might be used by multiple employees, multiple clients, or multiple contacts. A single address record might be used by a client and a contact or any combination thereof.

There is an XREF_Address_Employee table that links employees and addresses together.  There is an XREF_Address_Client table that links addresses and clients.  There is an XREF_Address_Contact table that links addresses and contacts. (Again, there are several other users of addresses, and thus several other XREF tables.)

This is how things exist today.  Any operation that is done for addresses (insert, delete, whatever) must have multiple stored procedures written, one for each variation of the XREF tables.

My intention is to create a single XREF table that can be used for all combinations.  Instead of having an XREF_Address_Employee table with fields FK_Address and FK_Employee and an XREF_Address_Client table with fields FK_Address and FK_Client and an XREF_Address_Contact table with fields FK_Address and FK_Contact, I want a single table XREF_Address_To_Owner with fields FK_Address, OwnerID, and OwnerType.  The OwnerID is an EmployeeID if OwnerType = 1, it is a ClientID if OwnerType = 2, etc.  With this arrangement I need only one stored procedure for each sort of operation instead of one for each variation of the XREF file.

The tables are used in such a way that one would typically search to see which addresses belong to a given employee/client/contact/whatever.  It would be very unusual to take and address and try to trace it back to its users.

In the old scheme, for XREF_Address_Employee, the primary key is (FK_Address, FK_Employee) and both of these are foreign keys.

In the new scheme, for XREF_Address_To_Owner, the primary key is (FK_Address, OwnerID, OwnerType) and FK_Address is a primary key.

If I want to find all addresses for employee 555, the old way I would do

Select a.*
From XREF_Address_Employee x
Join Address a on x.FK_Address = a.ID
Where x.FK_Employee = 555

The new way is

Select a.*
From XREF_Address_To_Owner x
Join Address a on x.FK_Address = a.ID
Where x.OwnerID = 555 and x.OwnerType = 1

The questions that I originally posed were:

What sort of performance hit might I incur because I am searching on "OwnerID and OwnerType" rather than "FK_Employee"?

and

Would I improve the search speed by creating a separate index on (OwnerID, OwnerType) even though they are part of my primary key?

Thanks.
0
 
LVL 25

Accepted Solution

by:
jogos earned 250 total points
Comment Utility
Q1 repeat:
- more data in table+PK -> more pages so more reads
- more chanse to have a query that access table with bad performance (maybe not the query's today, but the one you create tomorow or when #adresses off one type grows a lot)

Q2 extra index normaly could be helpfull if besides off the PK-columns  extra column(s) are added in the index to prevent full table scan
0
 

Author Closing Comment

by:TimAttaway
Comment Utility
The information was good but it didn't actually address the question that I asked.  I'm way beyond this in my design at this point, however.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now