1

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

  • 6AM - 10AM
  • 10AM - 1PM
  • 1PM - 4PM

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:

  • 6AM - 10AM
  • 10AM - 1PM
  • 1PM - 4PM

but if it was a Saturday, it might be:

  • 6AM - 8AM
  • 8AM - 12PM
  • 1PM - 2PM

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.

John Farrell
  • 24,673
  • 10
  • 77
  • 110
leora
  • 188,729
  • 360
  • 878
  • 1,366
  • I think the normalization is a bit overkill since these tables are so small (and are not likely to grow very big) – flipchart Oct 28 '11 at 03:59

1 Answers1

1

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.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • appreciate your point but i am trying to figure out how i would get the TYpe. I would need some other table that would have TypeId and TypeName (an example row would be M-F and 1 or something like that. The issue is that i still need to figure out how i can take a DateTime property and convert that into a Type to then retrieve the list of timeslots. I am now thinking i need to actually store DayOfWeek as an integer for this lookup ..thoughts? – leora Oct 28 '11 at 04:34
  • @leora the mapping of DayOfWeek vs Weekend should probably not be part of the data structure. That should be done in either a stored procedure or in the client code. Pretty much every db and language supports determining the that. For example http://stackoverflow.com/questions/400421/equivalent-of-weekday-function-of-vb6-in-c-sharp or http://stackoverflow.com/questions/4791153/select-daymonday-in-sql-server – Conrad Frix Oct 28 '11 at 05:37
  • i know but I still don't understand how that would translate to the Type column listed above. If its Thursday i need to retrieve the lists of hours for Thursday> How do i get from Thursday to Type = 2 ? – leora Oct 28 '11 at 11:18