Database MySQL > Storage > Opening Hours of Shop

Question

I have a database with shops and i would like to add their opening hours (start time - finish time from Monday to Sunday). Opening hours can be any value (incremental 15 minutes).

I have a table for shops (1), a table for days (2) and a table for opening hours (3) :

(1) shop table : shop_id;...

(2) day table : day_id;day_text

(3) opening hours table : shop_id;day_id;start_time;finish_time where start_time is a time and finish_time is a time

Should i create a fourth table for hours such as hour_id; hour_time (incremental 15 minutes)

I have a few million data so i am looking at an efficient way to do it so requests for searching shops opened in the area are quickly performed.

I'm not sure what you're asking here… Could you edit the question to make your actual question clear? — David Wolever
– David Wolever, Commented Sep 4, 2011 at 0:51

Randy · Accepted Answer · 2011-09-04 01:05:59Z

0

yes - your approach seems correct.

you may also care about weird 'days' like holidays that change the hours.

this might be good as an exception style table with date, and hours by store. then you would check the 'normal' hours and override if there was a special date.

answered Sep 4, 2011 at 1:05

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

julienp Over a year ago

Thanks. This is exactly what i did, exception table that has priority on the opening hours table.

Julien Over a year ago

how do you handle it when shop is open before midnight and close in the morning (like a bar) open from 22:00 till 04:00 ?

Randy Over a year ago

I suppose you could have open_date and open_time and close_date and close_time in your open_hours table...

lewiguez Over a year ago

@Randy, if you look at Google Transit Feeds, they have this kind of functionality by going over the "24" hour mark. It's all based around the date of the service. If you start operation at 5/7/2012 6:00am and end at 5/8/2012 2:30am, that day of service is still the same despite a different "day." So, they increment of the 24 hour time mark. That same service time would be 5/7/2012 06:00 to 5/7/2012 26:30. Not saying it's "right" or anything like that, but it's an option.

Collectives™ on Stack Overflow

Database MySQL > Storage > Opening Hours of Shop

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related