CODEWITHSUNDEEP
databaserelational-databaseentity-relationshipschedule
Ana Ban
Ana Ban

Reputation: 1405

Representing Work Hours/Schedule in Relational Database

I'm developing a searchable database for temps/part-timers, and I have no experience implementing their work hours availability. I have already developed the schema for the employee, skills, agent and customer. Now I need to capture the hourly availability of the employee.

The system should be able to represent any combination of 24/7 hourly schedules, i.e.:

and of course there will be the occasional outlier who will be available full-time, although I'm thinking in the employee table there will be a boolean field for full-time, and this schedule table/system will only be consulted if the employee is part-time.

Any experience/guidance/direction for this would be much much appreciated.

Upvotes: 0

Views: 4453

Answers (2)

CHaisPas
CHaisPas

Reputation: 1

Why not just creating an association betwen intervals and users on a time period ? If you know, it'a 1 week recurrent, you choose any given week. If you know, it's 1 month recurrent, you choose any given month etc...

StartAvailableDateTime1 DATETIME,
AvailableDateTime2 DATETIME,
UserID INT

Upvotes: -1

Stephen Senkomago Musoke
Stephen Senkomago Musoke

Reputation: 3523

I would model it by focusing on the availability for each day for each user with the following columns:

a) Day (1-7) where 1 is Sunday and 7 is Saturday following the MySQL conventions

b) Start time - starting time can be null if not available

c) End Time - end time can be null if not available

d) Notes - any notes on that day's availability

e) userid (the id of the user whose schedule is being shown)

f) Not Available - Yes/No

This means that each user will have 7 availability records. However on the user face you can carry out tweaks to have data copies from on day to another

Upvotes: 3

Related Questions