Mysql – Database for opening times of locations

database-designMySQLmysql-workbench

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?

Best 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.
Related Question