Multiple Rows or Multiple Columns???

  • 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

  • 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.

  • 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.


    And then again, I might be wrong ...
    David Webb

  • 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