Database for opening times of locations

Posted on

Question :

I’m designing a database for opening times and created this solution.

The specifications of the database which will be a MySQL are, that a location have standard opening times in a week and can have special opening times for example on christmas or something like that.

So if I want the opening times of an entry I would first search in ‘special_opening’ for the given day and if it given no results back, I would load the data from ‘opening’.

Is this a legit way to realize the database?
Are there better ways to do it?

Answer :

You could simplify your design and make it more powerful by keeping all of the opening times in a single table which includes a date range and a priority code for resolving conflicting records.

It would look something like this:

create table OPENING
( id INT
, entry_id INT
, from_date DATE
, to_date DATE
, priority INT
, weekday_id INT
, start TIME
, end TIME
, pause_start TIME
, pause_end TIME
, editor_id INT
, timestamp TIMESTAMP
)

The differences to note between your model and this one are:

  • You keep one set of records for each date range that applies instead of just having the current data.
  • You can keep a date range history of opening hours. This also allows you to future date ranges which is handy for getting data set up in advance.
  • You keep regular hours and exceptions in a single table, so you don’t need to have conditional logic in retrieving the opening hours.
  • Exceptions have a date range also, often a range of one day if that is what applies.
  • All records have a priority level. Regular records have a low priority, exceptional records have a higher priority. When reading just select top 1 order by priority descending.

Leave a Reply

Your email address will not be published. Required fields are marked *