Search code examples
databasedatabase-design

Storing Business Hours in a Database

Business A hours of operation:

  • Monday: 9am - 5pm
  • Tuesday: 9am - 5pm
  • Wednesday: 9am - 5pm
  • Thursday: 9am - 5pm
  • Friday: 9am - 5pm
  • Saturday: 9am - 12 Midday
  • Sunday: Closed

Data model:

CREATE TABLE "business_hours" (
    "id" integer NOT NULL PRIMARY KEY,
    "day" varchar(16) NOT NULL,
    "open_time" time,
    "close_time" time
)

The "day" is restricted to a choice of the 7 days of the week in code (through the ORM). If a business is closed on a certain day the open_time and close_time are NULL. It is related to the business through an intermediate (Many-to-Many relationship) table.

Something isn't right.

What are suggestions?

Solution

  • Overall, I see nothing wrong with this. Except...

    1. I would store the day of week as an integer using whatever numbering system your native programming language uses (in its libraries). This will decrease the size of the database and remove string comparisons from your code.

    2. I would probably put the foreign key to the business table right here in this table. That way you won't need a link table.

    So I guess I would do:

    CREATE TABLE "business_hours" (
     "id" integer NOT NULL PRIMARY KEY,
     "business_id" integer NOT NULL FOREIGN KEY REFERENCES "businesses",
     "day" integer NOT NULL,
     "open_time" time,
     "close_time" time
)

    In my business logic, I would enforce a constraint that every "business" has at least 7 "business hours". (At least because Jon Skeet is right, you might want holiday hours.) Though you may want to relax this constraint by simply leaving off "business hours" for days that the business is closed.