Modelling Time Slots in a Database
I need to create a database that has the following requirements:
1. Locations need to have customizable timeslots, to which actions can be assigned.
2. Timeslots are 30 minute intervals
3. Multiple actions can be assigned to single timeslots
I have thought of the following design, but I am unsure if this is the best way to do this. It seems this method would give a lot of redundancy.
In this scheme, everything in caps is a table, * means many, 1 means 1, --- stands for a relationship.
"ACTION" \*---* "TIME_SLOT" ---1 "DAY" ---1 "LOCATION"
Example: A location may have the following timeslots: 25-08-12 8:00-20:00, 26-08-12 10:00-20:00, 28-08-2012 10:00-12:00, 28-08-2012 12:30-20:00
Using my design, this example would:
- give 3 records in DAY for this location.
- give 24 records in TIME_SLOT for the first day
- give 20 records in TIME_SLOT for the second day
- give 19 records in TIME_SLOT for the third day