CODEWITHSUNDEEP
sqldatabase-designdata-modeling
Emil
Emil

Reputation: 8527

Design Hours of Operation SQL Table

I am designing a SQL table to store hours of operation for stores.

Some stores have very simple hours: Monday to Sunday from 9:30AM to 10:00PM

Others are little more complicated. Please consider the following scenario:

Monday:  Open All Day
Tuesday: 7:30AM – 2:30PM & 4:15PM – 11:00 PM 
Wednesday: 7:00PM – 12:30 AM (technically closing on Thursday morning)
Thursday: 9:00AM – 6:00PM
Friday: closed.

How would you design the table(s)?

EDIT

The hours will be used to showing if a store is open at a user selected time.

A different table can probably handle any exceptions, such as holidays.

The store hours will not change from week to week.

Upvotes: 8

Views: 3702

Answers (3)

FLOOD racer
FLOOD racer

Reputation: 193

Think of it more as defining time frames, days / weeks are more complex, because they have rules and defined start and stops.

How would you define a timeframe?

one constraint (Start[Time and Day]), one reference 'Duration' (hours, minutes,.. of the span)*. Now the shifts (timeframes) can span multiple days and you don't have to work complex logic to extract and use the data in calculations. 

**Store_Hours**

Store | Day | Open | DURATION
---------------------------
1     | 1   | 0000 | 24
1     | 2   | 0730 | 7
1     | 2   | 1615 | 6.75
... 
1     | 3   | 1900 | 5.5

Upvotes: 3

Cade Roux
Cade Roux

Reputation: 89651

Do you have to do more than just store and display it?

I think a design which needs to tell if a store is open at a particular time would have to be informed by all of the possibilities, otherwise, you will end up not being able to accommodate something.

What about holiday exceptions?

I would consider storing them as intervals based on a base time (minutes since time 0 on a week).

So 0 is midnight on Monday.

Interval 1 would be 0 - 1440

Interval 2 would be 1890 - 2310

etc.

You could easily convert a user selected time into a minute offset and determine if a store was open.

Your only problem remaining would be interpretation in display for friendly display (probably some extensive logic, but not impossible) and overlap at time 10080 -> 0.

Upvotes: 2

Chains
Chains

Reputation: 13157

A table like this would be easy for both the output you posted, as well as just firing a bit back (open? yes/no):

Store | Day | Open | Closed
---------------------------
1     | 1   | 0000 | 2400
1     | 2   | 0730 | 1430
1     | 2   | 1615 | 2300
...

Features:

  1. Using 24-hour isn't necessary, but makes math easier.
  2. Store ID would presumably join to a lookup table where you stored Store information
  3. Day ID would translate to day of week (1 = Sunday, 2 = Monday, etc.)

To query for your dataset, just: SELECT Day, Open, Close... (you'd want to format Open/Close obviously)

To query IsOpen?, just:

SELECT CASE WHEN @desiredtime BETWEEN Open AND Closed THEN 1 ELSE 0 END 
FROM table 
WHERE store = @Store

Upvotes: 9

Related Questions