Reputation: 196891
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.
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
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
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