1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
oren
  • 35
  • 5
  • On a given day, could a customer work a number of hours for one category and then work more hours for another category? If so, then the table which stores these values would have to have fields for company, category, date and hours_worked. – No'am Newman Jan 06 '15 at 09:18

1 Answers1

2

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    HOwever, if you do so then make sure to set a unique index for what wouod have been the compound key. – HLGEM Jan 05 '15 at 22:03
  • thanks marc, i need some more information. you mean that DailyWorkingHours will have it as a FK? each ID will be presented 7 times, one for each day in a week? – oren Jan 05 '15 at 22:08