CODEWITHSUNDEEP
mysqldatabase
bigLund8
bigLund8

Reputation: 119

Database Design: weekday and hour availability

I'm attempting to create a scheduling system based on a group's availability for a specific hour during a weekday. A group has the possibility of being available any hour for any day of the week. For example, Group A is available Monday 2AM to 8AM, 1PM to 11PM, Tuesday at ...

These times are important because there are over 400 groups and an administrator needs to schedule them for activities based on their availability. I've brainstormed 3 different options and read through many similar situations, but none really correlate.

All 3 of these options should work in theory, but which would you advise:

Option #1

Group Table
  -ID
  -Name
  -Availability (store as array) ["M0", "M1", "Su10"] where M represents Monday and 0 represents 12AM

Option #2 Group Table -ID

Group Availability Table
  -ID
  -GroupID
  -Monday (store as array) ["0", "1", "2"] where 0 represents 12AM, 1 - 1AM, etc.
  ...all 7 days of the week

Option #3 Group Table -ID

 Group Availability Table
   -GroupID
   -M0 (boolean)
   -M1 (boolean)
   ...all 168 possible weekday-hour combos

I don't think Option #1 is viable when the admin will be querying this data several times a day and Option #3 is a huge table although it's not very data heavy with just booleans. Thanks for the help.

Upvotes: 3

Views: 1808

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

I think you need to consider a time slot table. You would then have a join table to relate groups to this table

timeslots
------------
timeslot_id
weekday
hour

groups_to_timeslots
–------------
group_id
timeslot_id

The timeslots table would consist of 7 x 24 entries with your possible time slots. Your groups_to_timeslots table provides many to many mapping of groups to timeslots.

Upvotes: 4

Related Questions