DB Design for Schedule

Question

I'm creating a web application wherein users should be able to specify their weekly schedule. Here's my DB model for that:

id
user_id
day
from
to

day is an enum of 0-6 (0 for Sunday, 1 for Monday, and so on). from and to represent minutes from midnight. So, 0 means midnight, 1 means 12:01am, 2 means 12:02am, etc.

Is this a good design? Any potential problems with it?

What if I wanted the ability to display more than a week? How would I handle that? Right now, I'm just displaying weekly work schedule, but it might be nice to be able to specify more.

Answer 1

On a first thought I would try to make the design a bit more open. Something like

id
User_id
Date
Start_Time
End_Time

This way you do not limit yourself to one week. Besides, working with dates and times makes it easier to do calculations. Having them it is pretty easy to get the day of the week, minutes from midnight, etc.

Using this you can also easily get the week number which would allow you to show the user their schedule by just choosing the week of the year and/or its start dates.

This design supposes you will not have schedules starting on a day and ending on the next one. In that case you would need a Start_Date and an End_Date.

Hope this helps

Answer 2

Please don't reinvent the wheel. This is one of the most powerful thinking on programming and developing anything.

What I mean here is, everytime you have the possibility to rely on built-in or native type, you should use it. On fields day, from and to, you can and should consider using appropriate native types, like Date, Datetime, Timestamp.

Also, if you need retrieving, filtering and so forth on these data, you can use built-in functions.

This kind of thinking on design can improve reliability on data, and make you programming easier, hence you count on functions and data types proper, tested, and widely adopted.

I would do this database like:

id -- autoincrement
Start -- datetime
End --datetime

I suppose you have a Users table. And I would add one more table, UsersSchedule, like:

user_id -- FK to users
schedule_id -- FK to schedule

Thus, many users can be registered to the same meeting or event. Of course, this can go even further, and add other control fields like EventName, EventPromoter (user_id from proponent user), Invites(user-id list of people invited to event), Atendees (user_id list of people who confirmed/atended the event)... but this decision is up to you.

Note that I would heavily use DateTime functions (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) in order to save on logic programming, like check today's events scheduled for a particular user, conflicting events for a particular user, and so on.

Well, I think you got the idea.

EDIT

Supposing you have one entry like:

id   |        start        |        end
45   | 2010-10-25 09:00:00 | 2010-10-25 18:00:00

You can run the query:

SELECT CONCAT(
DATE_FORMAT(start, '%a'), ', ',
DATE_FORMAT(start, '%l%p'), ' - ',
DATE_FORMAT(end, '%l%p')
) as Event FROM schedule WHERE id = 45;

Which will give you the following output:

Mon, 9AM - 6PM

Done.

Answer 3

Okay, I think where the other answerers are getting off track is that they're trying to design a perpetual calendar. I think you only want to design a system that shows the regular, repeating weekly schedule for, for instance, a worker.

For that use, a regular, repeating weekly schedule, your design is okay. I would make FROM and TO into TIME columns rather than integers, but that's about it.

If you intend to track the amount of time people actually did work you will have to implement a second table for that more along the lines of what the other users have mentioned.