Part 1: Which MySQL data type to use for scheduling?
In Part 1 of my question, I asked which MySQL data type to use for, say, scheduling movies at a cinema. When scheduling a movie, you need to know the date (e.g. Jan 1, 2011) and time (e.g. 9:00 am). Please don't discuss part 1 of my question here.
Here's my part 2: what if I wanted to store a cinemas daily hours of operation? For example:
What is a good way to represent this in the database? Keep in mind that, in this case, the date does not matter (e.g. I don't need to know what date the particular Monday is on). I only case about days of the week and times of the day.
Here's how I would probably do it:
TABLE: schedule
- schedule_id
- day (ENUM: 0, 1, 2, 3, 4, 5, 6)
- start_time (INT)
- end_time (INT)
start_time and end_time would represent the number of minutes since midnight. So, 0 for midnight, 1 for 12:01 am, 2 for 12:02 am, and so on.
Am I approaching this right? Suggestions?
As I said in my answer to your first question, in my opinion a person should use the datatype that better fits and describes the reality. In this case I would like to use Time type for the start_time and end_time fields.
Instead for the day field I would like to use numbers from 1 to 7.