Hi,
In my shops database I need to have the opening hours. Do you have an idea how i can implement this in my dB?
The opening hours are from Monday to Sunday, each day can have 2 opening windows (ex 09:00-12:00, 16:00-19:00)
+3
A:
build another table, call it schedules, add a foreign key to the shops table primary key, a Day of week field, time_open, time_closed.
The data should look something like this:
shop_id day_of_week time_open time_closed
1 1 09:00 12:00
1 1 16:00 19:00
1 2 09:00 12:00
1 2 16:00 19:00
1 3 09:00 12:00
1 3 16:00 19:00
1 6 10:00 14:00
2 1 09:00 12:00
2 1 13:00 18:00
This will give you the opportunity to build any kind of schedules, with as many windows as you want, with how many exceptions you need. It's universal, limited only to the fact that it expects all weeks to be identical. No holidays considered, nor odd/even-week schedules that someone might use.
Alexander
2010-04-27 13:30:44
this will allow you to add a third schedule for a given day, if that's a violation of your rules, you'll need to tightly control the addition of new schedules to check for that... or you could add a "sched_num" column with a check constraint that it be either a 0 or 1 and a unique constraint on day_of_week and sched_num.
Stephanie Page
2010-04-27 19:07:29
+2
A:
What type of database are you using? If it's mysql, put in a field of type "time". Then you can pass it the times and do basic sql time manipulations on them. This whole post assumes mysql.
If there is only one store and you know it will never grow, just make a table called times where you keep the times the store is open. Then, if you want to see if the store is open just check the current time against one of the ranges in the database.
If there is more than one store, make a table called "times" and a table called "stores". In the times table, columns can be "open" and "close" with an id called "store_id". The stores table needs an id too. Then, you can just say:
"SELECT open, close FROM times WHERE store_id=x"
This will give you ALL the time ranges associated with the particular store.
DexterW
2010-04-27 13:30:53