3

Does it make any sense to use bitfields to store and manage a schedule?

I'm working on a Ruby on Rails application to handle restaurant opening hours and reservations, and I'm having some difficulty modeling schedule.

Each restaurant will have opening hours (like Monday 9am-12pm and 2pm-5pm) each day, and each table in the restaurant will have a size (2, 4, 8-seat, etc.) and its own openings.

So far, I've been using two tables to keep track of things:

opening_hours

  • day_of_the_week (string)
  • starts_at (time)
  • ends_at (time)

bookings

  • table_id (int)
  • starts_at (datetime)
  • ends_at (datetime)

With those tables, I can make sure new bookings don't overlap other bookings for the same table and that the booking falls within an opening-hour range for that day of the week.

It's problematic to find the open slots in the schedule, though. That is, given a set of opening hours and existing bookings, where are the gaps that could accommodate new bookings?

While looking through StackOverflow for inspiration, I came across this comment about using bitfields for schedules, and it piqued my curiosity. I don't really know anything about bitwise logic, but I wonder if I could replace the above tables with something like:

opening_hours

  • day_of_the_week (string)
  • hours (96 bits, representing open/closed times for each quarter-hour of the day)

bookings

  • table_id (int)
  • date (date)
  • hours (96 bits, representing available/booked times for each quarter-hour of the day)

And then I could use bitwise logic (waves hands) and find the open, available times for a given day.

So my questions:

  • Would it make sense to do something like this?
  • Can anybody point me to a blog post or tutorial covering using bitfields for schedules?
  • What else should I look at to learn about bitfields & bitwise logic, specifically in the ruby/Rails realm?

thanks,

Jacob

Community
  • 1
  • 1
trisignia
  • 1,173
  • 1
  • 10
  • 19

1 Answers1

0

This seems like a strange way to store what are dates.

Ask yourself what the behavior of the Restaurant or OpeningHours will be? I bet you'll find yourself converting back to real date objects to implement that behavior. So why use a weird encoding in the database (day of week and bit string)?

Additionally, do you ever intend to use SQL date operators to find what Restaurants or tables are open? That just got a whole lot trickier if it's possible at all with your encoding scheme.

I doubt you want to do this.

Winfield
  • 18,985
  • 3
  • 52
  • 65
  • I think you're right -- it's best to use regular datetimes here. I might end up converting to bitfields to more easily find the gaps in the schedule, but for most operations, it's easier to compare datetimes. – trisignia Aug 26 '11 at 13:33