Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
thanks trygvis for uploading the image –  Kingalione Apr 27 '13 at 18:40
add comment

1 Answer

up vote 0 down vote accepted

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.
share|improve this answer
    
Thats a great solution! I will discuss this with my teammates later. Thanks Joel Brown. –  Kingalione Apr 28 '13 at 3:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.