DB design Employee working schedule

Monday, July 7, 2014

I am designing a employee DB. I wanted to store their availability time for next 3 weeks. Here are my requirement.



  1. Employee standard working time is defined in table or field (mostly between 9 to 5)

  2. Some employee work on different time slot (like between morning 7 to 3)

  3. Sometime employee might split their working time (morning 7 to 11 & then evening 5 to 9)


I want to store these in DB so that I can see their calendar and know their availability. Here is my design.


EmpID - int Date - Date StartTime - Time EndTime - Time


Table sample



1 | 8.12.2014 | 9:00 | 17:00

2 | 9.12.2014 | 9:00 | 17:00

2 | 9.12.2014 | 7:00 | 15:00

1 | 10.12.2014 | 7:00 | 11:00

1 | 10.12.2014 | 15:00 | 19:00


Here default time of employee 2 is 9 to 17. Creating default entry for each employee in 3 week advance. Now for 9.12.2014 employee 2 wants to work from 7 to 15. In this case I will create additional entry in table which will override the previous schedule.


I am creating daily calendar for each employee for 3 weeks in advance.


Questions




  1. Since I am creating daily calendar for all employee 3 weeks in advance it is going to be heavy load on DB.




  2. This is not a efficient way. Since it's demoralized I will be able to get details easily but it's difficult to maintain.




Please suggest me some good DB design for this kind of employee availability problem.







http://ift.tt/1xFhtxs