CODEWITHSUNDEEP
sqlpostgresqldatedatabase-designschedule
Dhiren
Dhiren

Reputation: 602

DB design Employee working schedule

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

EmpID | Date        | StartTime | EndTime
-----------------------------------------
    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.

Upvotes: 0

Views: 3922

Answers (1)

Dhiren
Dhiren

Reputation: 602

I think this DB design will work. Though I required to populate the Data in advance and it might create some duplication related to data. But that will make retrieval faster because my application is going to frequently retrieve the calendar data.

Upvotes: 2

Related Questions