Database design for Time Sheet adopting different weekend

database-design

My Requirement
I need to design a database that handles multiple time sheet formats for their staff\contractor working with different clients. Each of them may have a different week end say

Client 1 ==> Saturday & Sunday
Client 2 ==> Friday & Saturday
Client 3 ==> Sunday & Monday
Client 4 ==> Saturday & Sunday
Client 5 ==> Saturday & Sunday
Client .. ......................................................

My Query

How should I design the DB to implement this?

I will be having a Timesheet table.

I will be also having a Holiday table to include the public holidays for different clients.

Where can I design the table to include the different week ends?
Please suggest.

Update

I need to implement the below – to restrict or alert weekend day – while allowing option to add the time sheet data. The weekend days are different (targeting different time sheet format) for each staff as per the client they are working with.

enter image description here

So as per your suggestion Joel, including the table WeekDay to my design, the below way is the option you recommend to implement different weekends?

Timesheet ==> TimesheetID,EmpID,SubmittedDate, ClientID ...
Timesheet_Details ==> Timesheet_Details_ID,TimesheetID,Date,WeekDayID,IsHoliday
Client ==> ClientID,TimesheetformatID,..
...
...
...
Holiday ==> HolidayID,HolidayDescription,Date,ClientID ...
WeekDay ==> WeekDay_ID,DayName,DayNumber,ClientID...

Best Answer

You don't say so explicitly, but I think maybe your concern is that you want to have a timesheet that has one column per day and you don't know what to do about inconsistent weekends.

If that's the case, then you're approaching it incorrectly. Don't have columns for days. Have rows in a details table for days.

Your timesheet should use individual entries at a date level, assuming you only want one entry per day. If you want multiple entries per day (per worker) then have one entry per date and start time.

For weekends you keep a separate table for working days (of the week) per worker.

Consider something like this:

ERD

Note that the WEEKDAY and TIMESHEET_STYLE_DETAIL tables (in blue) are a bit contrived. You could these tables, but really in practice I think a lot of people would probably use something like a bit flag array in the TIMESHEET_STYLE table containing a flag for each day of the week instead.

Note however that some of your SQL queries would actually be simpler if you did have an explicit WEEKDAY table and an intersection table (TIMESHEET_STYLE_DETAIL) that contained a row for each day of the week. This way SQL functions that convert a date into a day of week number could be used as part of joins against this table.

Related Question