Hi,
I am trying to store business opening hours in a table and I think I am over complicating it.
As you can see below, the hours run from one date to another. what I need to work out is; if I insert a set of hours, eg a bank holiday (which has different hours of opening), should I
- store them as a new record and perhaps add a new column to store ‘Saint Patrick’s Day’ for clarification?
OR - store it as a new record and then do two other things
a. set the end date of the generic/default time period to be the previous day AND
b. add a new record of default hours, running from the day after the holiday until the original end date?
CREATE TABLE business_hours (
hours_id bigint(20) NOT NULL auto_increment,
business_id int(11) NOT NULL,
start_date date NOT NULL,
end_date date NOT NULL default '0000-00-00',
day_of_week varchar(99) collate utf8_unicode_ci default NULL,
opening time default '00:00:00',
closing time default '00:00:00',
PRIMARY KEY (hours_id),
UNIQUE KEY businesses_hours (business_id,day_of_week,opening)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table 'business_hours'
--
INSERT INTO business_hours (hours_id, business_id, start_date, end_date, day_of_week, opening, closing) VALUES
(16, 477, '2010-11-01', '0000-00-00', 'Monday', '08:30:00', '09:30:00');
If I were recommended to use option 2 the table data would be like this
(16, 477, '2010-11-01', '2010-03-16', 'Thursday', '08:30:00', '17:30:00');
(17, 477, '2010-03-17', '2010-03-17', 'Thursday', '10:00:00', '16:00:00');
(18, 477, '2010-03-18', '2010-12-31', 'Thursday', '08:30:00', '17:30:00');
bazz