Table data / storage question

IBazz · November 8, 2010, 10:44am

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

r937 · November 8, 2010, 2:14pm

i am a very firm believer in simplicity

the mere fact that you have a start date and end date will give you countless headaches

use a calendar table:

CREATE TABLE business_hours 
( business_id      INTEGER NOT NULL
, calendar_date    DATE    NOT NULL
, PRIMARY KEY ( business_id, calendar_date )
, opening          TIME        NULL 
, closing          TIME        NULL 
);

notice there is no need for any special column for st patrick’s day

you will be delightfully surprised at how simple the queries to retrieve opening hours will turn out to be

holler if you do not see how march 17 would be represented

IBazz · November 8, 2010, 4:29pm

thanks rudy.

i am a very firm believer in simplicity

Me too. I just find the road very complicated

You suggestions does indeed, seem nice and simple from the storage perspective. Maybe it’s my ‘plan’ for outputting the opening hours which has been wrong/complicated. I mean; I had hoped to output them like this:

Normal hours:
from ‘date’ until ‘other date’
opening time:
closing time:

Holidays:
date:
opening time
closing time

I expect there is a query ( which I can’t yet work out ), which would be able to differentiate between the majority of hours (which are the same) and those dates which have unusual hours?

something like


select 
  date
, opening_time
, closing_time
from business_hours
where business_id = 456
group by opening_time

?
But how would I determine which is the ‘standard’ pair of hours?
bazz

r937 · November 8, 2010, 5:06pm

you did not state this requirement originally

[indent]I am trying to store business opening hours in a table [/indent]no mention of “standard”, just how to “differentiate between the majority of hours (which are the same) and those dates which have unusual hours?”

IBazz · November 8, 2010, 5:22pm

oops, I thought I had explained it;

In my first post, I was trying to find how to differentiate between the normal hours and the ones specific to a date or holiday.

So if I follow your calendar suggestion, the script for inputting ‘normal’ hours could set the value in the (new) date_type column as ‘default’. yes? And then the script for inputting holiday dates could set that date_type column to holiday?

dat OK?

bazz

r937 · November 8, 2010, 5:34pm

not to me

but go ahead and load up your table, write a few queries, and let us know how it works out for you

IBazz · November 9, 2010, 2:55pm

groan. my head exploded last night.

OK, so I can store the hours like this


| business_id |  hours_type         |  opening  | closing   |
|       1     |  normal             |  09:00:00 | 17:00:00  |
|       1     | Saint Patrick's Day | 12:00:00  | 12:01:00  |

But imagine looking up a website for your trip later next year. standardised hours won’t do because they aren’t flexible enough to show what they are today and what they will be when I might visit. So I need to put the date in somewhere. And then I need to show the day because the hours may be different from day to day durihng the same week.

so then we get to this:


| business_id | start_date |  hours_type         |  opening  | closing   | day_of_week |
|       1     | 2010-11-01 |  normal             |  09:00:00 | 17:00:00  | Monday     |
|       1     | 2011-03-17 | Saint Patrick's Day | 12:00:00  | 12:01:00  | Thursday    |
|       1     | 2011-02-01 | normal              |  10:00:00 | 16:30:00  | Monday      |

So, now, I want to query the db for those details and, also, to make up an end date for the first set of normal hours, based on the start_date of the second set.

Question:

my sub select doesn’t get back the correct min date so I need help with that
how might I get this to create an artificial end date, based on the nbh.start_date - 1 ?


select
  bh.start_date
, bh.hours_type
, bh.opening
, bh.closing
, nbh.start_date

from business_hours AS bh
inner join ( select nbh.business_id
                  , nbh.start_date 
                  from business_hours AS nbh 
                  where nbh.start_date = (select min(start_date )
                                          from business_hours
                                          ) 
                 
             ) as nbh
            on nbh.business_id = bh.business_id
           and nbh.start_date > bh.start_date
		where bh.business_id = 1

bazz

IBazz · November 9, 2010, 3:14pm

made progress

Can anyone cofirm this query to be correct, please? and if you can help me tweak it to return the nbh.statrt_date - 1 that would be very kind.


SELECT 
           bh.start_date
         , bh.hours_type
         , bh.opening
         , bh.closing
         , nbh.start_date
   FROM business_hours AS bh
 INNER JOIN (
       SELECT nbh.business_id
                , nbh.start_date
          FROM business_hours AS nbh
        WHERE nbh.start_date > (
                    SELECT min( start_date )
                       FROM business_hours )
                 ) AS nbh ON nbh.business_id = bh.business_id
            AND nbh.start_date > bh.start_date

  WHERE bh.business_id = 1

that brings back


Full Texts
start_date  	hours_type 	opening 	closing 	start_date
2010-11-01 	normal        	08:30:00 	   09:30:00 	2011-02-01

bazz

IBazz · November 9, 2010, 3:24pm

Blimey, at last, I got it?


SELECT bh.start_date
        , bh.hours_type
        , bh.opening
        , bh.closing
        , DATE_ADD(nbh.start_date, INTERVAL -1 DAY) as end_date

     FROM business_hours AS bh
   INNER JOIN (
        SELECT nbh.business_id
             , nbh.start_date
          FROM business_hours AS nbh
         WHERE nbh.start_date > (
                SELECT min( start_date )
                  FROM business_hours )
               ) AS nbh 
       ON nbh.business_id = bh.business_id
      AND nbh.day_of_week = bh.day_of_week
      AND nbh.date_type = bh.date_type
      AND nbh.start_date > bh.start_date
    WHERE bh.business_id = 1

bazz

r937 · November 9, 2010, 7:01pm

i’m happy for you

with my method, i would just show the opening hours for whatever dates the user selects

your solution looks over-complicated to me, and i’ll bet it’s fragile

IBazz · November 9, 2010, 10:36pm

Thanks rudy.

I’ll check for fragility but with unqiue indexing and specific scripting, I think it’ll turn out to be robust and hopefully bulletproof. Only then will it be ‘done’.

bazz