CODEWITHSUNDEEP
database-designdatabase-schema
leora
leora

Reputation: 196891

What would be the best table schema for storing values for different days of the week?

I have a website where you can choose a timeslot via a dropdown box. The items in the dropdown box would be something like:

i store this in a database table with 2 columns. Id and Description. (so i am simply storing the string "6AM - 10AM" in a varchar field)

I now need to add a datepicker so the user can pick a date AND a timeslot. I got a datepicker all wired up and then i realized that i have a new requirement:

I need to support a different set of timeslot depending on the day. So for example, if it was a friday, it might be:

but if it was a Saturday, it might be:

I am trying to figure out what new tables i need to store this information. My initial thoughts were.

  1. Don't create any new tables but simple add an extra field called DayOfWeek and when i need to load up my dropdown i query based on the current day of the week and just pickout timeslots

  2. Create new table to try to join days of week with time slots to have it more normalized.

I wanted to see if people had feedback on what would be an ideal database table design for storing this information.

Upvotes: 1

Views: 839

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52675

Option 1

Lets say you have this structure

Hours      |  WeekDay | Weekend
-------------------------------   
6AM - 10AM |     1    |   0
10AM - 1PM |     1    |   0 
1PM - 4PM  |     1    |   0
6AM - 8AM  |     0    |   1
8AM - 12PM |     0    |   1
1PM - 2PM  |     0    |   1

Even with two types the SQL looks pretty messy

Select  Hours 
From
      hoursTable  
Where 
     (weekDay = 1 and {WeekDayParam} = 1)
     or
     weekEnd = 1 and {WeekEndParam} = 1

Option 2

If it looks like this 

Hours      |  Type  
---------------------   
6AM - 10AM |     1 
10AM - 1PM |     1    
1PM - 4PM  |     1 
6AM - 8AM  |     2   
8AM - 12PM |     2   
1PM - 2PM  |     2

Then the sql is much more straight forward

 Select  Hours 
 From
      hoursTable  
 Where 
     type = {typeParam}

How do we deal with new requirements

How about we add another variant: holidays

Would you want 

Hours        |  WeekDay | Weekend | Holiday 
-----------------------------------------  
6AM - 10AM   |     1    |   0     |  0       
10AM - 1PM   |     1    |   0     |  0
1PM - 4PM    |     1    |   0     |  0
6AM - 8AM    |     0    |   1     |  0
8AM - 12PM   |     0    |   1     |  0
1PM - 2PM    |     0    |   1     |  0
10:30PM  2PM |     0    |   1     |  0

Or

Hours        |  Type  
---------------------   
6AM - 10AM   |     1 
10AM - 1PM   |     1    
1PM - 4PM    |     1 
6AM - 8AM    |     2   
8AM - 12PM   |     2   
1PM - 2PM    |     2   
10:30PM  2PM |     3

Notice how in the first solution every time you get a new hours policy you have to update the data structure the sql and the calling code but in the second you don't?

Go with the second solution.

Upvotes: 1

Related Questions