May 20, 2005 at 10:34 am
Hello All,
I have what I hope is a simple question and I hope I'm posting it in the correct forum.
We have an existing table and we are trying to improve performance on it. Right now as it exists an employee can pick up to 5 locations they would like to work in. Is it better to have a table like this where an emplyee would have multiple rows and one location associated with each row:
Employee_Preferred_Locations_ID int IDENTITY(1,2) NOT FOR REPLICATION,
Employee_ID int NOT NULL,
Location_ID int NOT NULL,
date_entered smalldatetime DEFAULT getdate() NULL,
date_last_updated smalldatetime NULL
-OR-
Where each employee has one record, with multiple locations associated with it:
Employee_Preferred_Locations_ID int IDENTITY(2,2) NOT FOR REPLICATION,
Employee_ID int NOT NULL,
Location_1_ID int NOT NULL,
Location_2_ID int NOT NULL,
Location_3_ID int NOT NULL,
Location_4_ID int NOT NULL,
Location_5_ID int NOT NULL,
date_entered smalldatetime DEFAULT getdate() NULL,
date_last_updated smalldatetime NULL
Essentially we would be doing multiple “OR”s in our sql statement. i.e ((Location_id1 in (13,14,15)) or (Location_id2 in (13,14,15)) or (Location_id3 in (13,14,15))). I know ORs are expensive performance wise. I don’t know which would be better.
Side note this table currently has close to 2 million rows.
Any recommendations or comments would be greatly appreciated and I thank you in advance for your assistance.
Barbara
May 20, 2005 at 11:10 am
If I were doing this, I would definitely normalize the tables (your first option, I believe). I would have an Employee Table, Location Table, and an EmployeeLocation Table. The EmployeeLocation table would hold the combinations of employees and their desired locations. Your second option above would require them to list all five preferred locations, instead of one to five locations. The OR logic would be a pain as well.
With a normalized table, you could do something like Select Location where LocationID in (select LocationID from Location where EmployeeID = xxxxx). Much better from a performance view than multiple views ORs.
May 20, 2005 at 11:53 am
Option 1. Normalize the tables. It's more flexible, more true to the relational model, easier to enforce integrity upon, and will save you a lot of work if the business rules change so that they ever get to pick more than 5 or less than 5 locations.
May 23, 2005 at 9:23 am
Chris & David,
Thank you both very much for taking the time to respond to my posting.
The information you both provided is very helpful.
Thanks again,
Barbara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply