I am presently working out the easiest method to store a business' hrs of operation inside a database.

For instance:

Business A has got the following hrs of operation

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

Presently I am possess a data model like the following

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

in which the "day" is fixed to a range of the seven days each week in code (with the ORM). To check if your clients are closed on the certain day it inspections when the open_some time and close_time are NULL. It relates to the company via a intermediate table (Many To A Lot Of Relationship).

Does anyone have recommendations for this database plan? Something about this does not appear to me.

Overall, I see no problem with this particular. Except...

  1. I'd keep day's week being an integer using whatever numbering system your native programming language uses (in the libraries). This can decrease how big the database and take away string evaluations out of your code.

  2. I'd most likely place the foreign answer to the company table the following within this table. This way you will not require a link table.

And So I guess I'd 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

During my business logic, I'd enforce a constraint that each "business" has a minimum of 7 "business hrs". (A minimum of because Jon Skeet is appropriate, you may want holiday hrs.) Though you might want to relax this constraint simply by departing off "business hrs" for several days the clients are closed.

It kind of is dependent on what you ought to store it for and exactly what the real-world data could seem like.
If you want to have the ability to determine whether the company is open in a certain point it might be a little awkward to question the plan as organized. More to the point, though, is: Can you ever have to look after a mid-day closure?

Some options include

  • A plan like that which you have, however with the choice to possess multiple periods for the similar day. It might look after the lunch time, but will make it awkward to operate a question that provides the opening hrs for any given day, say for presentation to some user.
  • A bitmap style approach "000000000111111110000000" for 9-5. The down-side for this approach is you need to select a specific granularity, i.e. whole hrs or half-hrs or, indeed, minutes. The finer the granularity, greater the information would be to read for any human. You could utilize bitwise operators to keep this value like a single number as opposed to a string of integers, however it affects legibility.

Might consider invoice discounting in holidays by including additional fields for month of the yearOrday time of month/week of month. Week of month has some minor subtlties "last" could for instance be week four to five with respect to the year.