> in national holidays etc..
If all the stores are in one country, here is one solution---keep a table of holidays(d date), populate it with dates for xmas, the national holiday, labour day &c, then populate the hoursopen table for each store with something like this ...
insert into storesopen
select null, s.sid, c.d, '08:00:00', '05:00:00'
from stores s
cross join calendar c -- insert a row for every combo of store row and date
left join holidays h on c.d=h.d -- exclude holidays
where weekday(c.d) < 5 and h.d is null;
...left join holidays h ... where h.d is null... is an
exclusion join, read about 'em, it will skip the calendar rows that have matching dates in holidays.
If the default is to have 2 open periods a day, run that again for the second open period.
Then for store owners, build a monthly calendar interface from a query like ...
select c.d, s.open,s.close
from calendar c
join storesopen s
where s.sid=_STORE_ID_ and year=2016;
> automatically the fields are populated with the current time/time of insertion.
A column created as time default null will not do that.
Edited 1 time(s). Last edit at 04/11/2015 12:48AM by Peter Brawley.