Database Design - Best way to show available hours?

Answer 1

With Microsoft SQL Server database you could store that data as typed XML and still be able to sort and serach data based on one or more values in that field. Make any calculations if needed etc.

A value in that column could look similar to this:

<businessDays>
    <monday>
        <hours from="12" to="15" />
        <hours from="16" to="21" />
    </monday>
    <friday>
        <hours from="13" to="15" />
        <hours from="16" to="18" />
        <hours from="19" to="21" />
    </friday>
</businessDays>

References on Typed XML:

• Typed XML in SQL Server 2005
• Typed XML Compared to Untyped XML

References on indexing and quering XML data in SQL Server:

• XML Queries and Indexing in SQL Server 2005
• SQL Server 2005 XQuery and XML-DML - Part 1

In your application you could serialize/deserialize this data into and from business objects.

Extremely simple and efficient.

Answer 2

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.

Answer 3

A minor tweak to Scotty Allen's model:

business table:
id - int
business_name - string
open_hour_range table:
id - int
business_id - int //foreign key to business
days_of_week- int // (bitmask) 1-127
open_time - time
close_time - time

Answer 4

I wolud rather use datetime in columns open_time and close_time. Its for meetings that will start at night and will end in the morning next day. :)