sql-serverdatabase-designcomposite-primary-keycompound-key

SQL Server : database design - composite primary key

I need your suggestion on how to implement SQL Server table relationships.

  1. I have many customers, each holds a unique id (customerID)

  2. each customer can have many categories associated (categoryID)

  3. each customer can have many sub categories (subCategoryID) for

  4. when a customer logs in I know its CustomerID, CategoryID and SubCategoryID.

  5. customer plans in advance how many hours will work every week for the year to come (on december 2014 plans 52 weeks of 2015)

  6. every day a customer reports if they worked or took a day off.

I thought of having a table called WeeklyPlanning with columns:

CustomerID, CategoryID, SubCategoryID, Year, WeekNumber, WorkingHoursPlan

and another table called DailyWorkingHours with columns:

Dates, WorkingHours

My questions:

I don't know how to combine these two tables together, use a compound key? (CustomerID, CategoryID, SubCategoryID, Year, WeekNumber) or maybe generate a unique PK in WeeklyPlanning that will be used as a FK in DailyWorkingHours?

I'm looking for the best way to implement this.

thanks

Solution

  • Do you really want to specify FIVE column's values for each JOIN between those two tables? That's what you'll have to do if you have a compound primary key made up from five columns - you need all those columns in your child table as well, and you need to always specify all five columns when doing a JOIN ..... ouch ......

    If not - use a surrogate column in WeeklyPlanning (a WeeklyPlanningID INT IDENTITY(1,1)) to simplify your life significantly!