Database Design - Best way to show available hours?

Question

I am interested in seeing suggestions for a database design regarding business hours.

It would be quite similar to what Facebook has - alt text http://uploader.ws/upload/200903/widget.png

I have a list of businesses, and I would like for users to be able to input multiple sets of available hours for that business. e.g.,

Monday: open 9-5; Tuesday: open 9-12; 1-5; etc. I would not like to be limited to two sets of hours per day. Ideally, N sets of hours per day. If that's not practical, no more than 4... no less than 2.

I am interested in the "best" (theoretical) and the most practical solutions.

The DBMS I'm using is MySQL.

Answer 1

How about:

create table business (
  id int not null auto_increment primary key,
  name varchar(255)
);

create table open_hour_range (
  id int not null auto_increment primary key,
  business_id int,
  day_of_week tinyint, /* 0-6 */
  open_time time,
  close_time time,
  foreign key(business_id) references business(id)
);

This allows you any combination of hours, including multiple per day. However, it may be a bit slow from a querying perspective, in that you'll need to do a fair amount of joining to come up with the list of what hours a business is open.

Also, if you want to be able to display hours in a format like:

M-F 9-5 Sa-Su 9-12

You'd need to merge similar ranges in code, outside the database. If you wanted this sort merging, you could change day_of_week to a start_day and an end_day.