Storage of working hours in the database

I'm currently trying to develop a better way to store hours of work in a database.

For example:

Business A has the following business hours

  • Monday: 9:00 - 17:00
  • Tuesday: from 9:00 to 17:00
  • Wednesday: 9:00 - 17:00
  • Thursday: 9:00 - 17:00
  • Friday: from 9:00 to 17:00
  • Saturday: 9:00 - 12 noon
  • Sunday: Closed

I currently have a data model similar to the following

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

where the "day" is limited by the choice of seven days of the week in the code (via ORM). To check if a business is closed on a specific day, it checks whether open_time and close_time are NULL. This is related to business through an interim table (many-to-many relationship).

Does anyone have any suggestions on this database schema? Something about this is not like me.

+68
database database-design
Jun 24 '09 at 5:56
source share
5 answers

All in all, I see nothing wrong with that. With the exception of...

  • I would save the day of the week as an integer using any numbering system used by your native programming language (in its libraries). This will reduce the size of the database and remove string comparisons with your code.

  • I would probably put the foreign key in the business table right here in this table. This way you do not need a link table.

So, 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 force the restriction that every β€œbusiness” has at least 7 working hours. "(At least because John Skeet is right, you may want to have holiday hours.) Although you can relax from this restriction, just leaving β€œworking hours” for a few days when the business is closed.

+47
Jun 24 '09 at 6:01
source share
β€” -

One of the situations not covered by this scheme is several opening periods in one day. For example, a local pub is open 12: 00-14: 30 and 17: 00-23: 00.

The box office may be open for morning evenings and evening performances.

At this point, you need to decide whether you can have multiple entries on the same day or if you need to present different hours on the same line.

How about opening times that cross midnight. Let's say the bar is open from 19:00 to 02:00. You could not simply compare the opening and closing times with the time you want to check.

+21
Aug 10 2018-12-12T00:
source share

It depends on what you need to store and what the data in the real world might look like.
If you need to determine if a business is open at some point, then it may be a little inconvenient to request a scheme, as indicated. More importantly, however, there is: will you ever need to provide a mid-day close?

Some options include;

  • A scheme similar to the one you have, but with the ability to have multiple periods on the same day. This would serve as a lunch break, but it would be inconvenient to run a query that gives you hours of work for a certain day, for example, to present to the user.
  • Raster style approach; "000000000111111110000000" for 9-5. The disadvantage of this approach is that you need to select a specific detail, i.e. Whole hours or half an hour, or indeed minutes. The finer the granularity, the more difficult it is to read data for humans. You can use bitwise operators to store this value as a single number, not a whole chain, but again it infringes on readability.
+10
Jun 24 '09 at 6:14
source share

I found out that if you want the Google data markup to recognize your data, you should follow these guidelines:

https://schema.org/openingHours

http://schema.org/OpeningHoursSpecification Contains "valid dates", which is very useful for some companies.

https://schema.org/docs/search_results.html#q=hours

You should have everything in order without a primary key, if you do not allow companies to share the same hours with the connection table - it is interesting that in the end you will have a finite number of combinations; I'm not sure how much it will be: p

In one of my projects, I used columns:

[uInt] business_id, [uTinyInt] day, [char (11)] timeRange

If you want to support the OpenHoursSpecification function, you need to add validFrom and validThrough.

The time range is formatted as follows: hh: mm-hh: mm

Here is a function that analyzes it, you can also change this function to analyze only one open / closed if you store them as separate columns in the database.

From my experience, I would recommend that you allow several times during the day, to give the opportunity to find out if they are closed on the same day or open 24 hours or 24/7. I had my opinion that if there was a day in the database, then that day the business was closed.

 /** * parseTimeRange * parses a time range in the form of * '08:55-22:00' * @param $timeRange 'hh:mm-hh:mm' '08:55-22:00' * @return mixed ['hourStart'=>, 'minuteStart'=>, 'hourEnd'=>, 'minuteEnd'=>] */ function parseTimeRange($timeRange) { // no validating just parsing preg_match('/(?P<hourStart>\d{1,2}):(?P<minuteStart>\d{2})-(?P<hourEnd>\d{1,2}):(?P<minuteEnd>\d{2})/', $timeRange, $matches); return $matches; } 
+8
Nov 21 '14 at 9:14
source share

Perhaps think about factoring during the holidays by including additional fields during the month of the year / day of the month / week of the month. The week of the month has some minor subtleties "last", for example, there may be week 4 or 5 depending on the year.

0
Jun 24 '09 at 7:09
source share



All Articles