Representing time periods in the UI and database

Question

I've recently adopted a project with an Employee model that needs to contain the person's available hours as an attribute.

The existing form uses 168 checkboxes to represent each hour in the week, and stores the information as seven 24 bit binary strings in the database, each bit acting as a boolean true or false for its corresponding hour in that day.

I'd really like to transition to something a little more elegant and manageable, but I haven't been able to come up with any simple solutions that match the existing implementation's flexibility.

Storing time periods as start and end times can be just as tedious to input when there can be multiple per day, and would likely make querying for availability at a particular time more complicated.

Is there a best practice for dealing with this type of information, both in the user interface and the database structure?

Answer 1

I would model the data in the database this way.

There's a many to many relationship between the employees and hours for each day of the week.

On the UI side, you could use checkboxes for the days and multiselect list boxes to set the hours for the given day.

Answer 2

Could you just make time period blocks?

Employee
  Availability
    7AM -> 12PM
      Monday
      Tuesday
      Wednesday
    1PM -> 4PM
      Monday
      Tuesday
    1PM -> 5PM
      Wednesday

Each user would have a list of time blocks that represent one or more hours throughout the day. Each time block could also represent one or more days of the week. Depending on how complex the users availability is there could be very little data or a lot.

The UI would not really have to change if you didn't want to as you could just figure out what check boxes are checked and build out a time period block. If there is a gap of one or more hours between times it would just become another time period.

Adding Shift UI :: http://imm.io/6vGk

Displaying Employee Shifts :: http://imm.io/6vGv