Database design: handling time

Question

I am developing a weekly timetable which ideally would allow teachers pick what subjects they teach. While most of this is fairly straightforward, from a conceptual point of view I am unsure how to handle time (which is, naturally, a fairly crucial aspect of the database).

Nobody can teach more than one thing at a given time, so having time as a primary key makes sense. Classes work on an hourly basis so integers are acceptable to this end. Times don't change from week to week.

While this seems fine on paper (and there is a quick mock-up of the database in Access below), this nonetheless has some additional baggage.

Monday 9am == 9. Monday 2pm == 14, etc. Cool. But Tuesday 9am == 33 and Tuesday 2pm == 38. A little bit awkward. Also, if one is using the integers for simple calculation (worked between 10 and 14?... 4 hours worked... correct), this can be easily messed up (worked all of monday and tuesday?... 48 hours worked... incorrect). But is integer still the best option for this type of data (given that date is unimportant)?

Data relating to class times will likely have to be manually input into the database. The database may have to be able to provide some sort of automation (automatically assign teachers to modules) and/or allow users to select modules to teach. RDMS likely to be MySQL.

Answer 1

Have a Class table, with a foreign key to Teacher - the person who taught the class. Give Class columns for the start and end time. This can be either the MySQL time type (09:00:00 for 9am) or an integer type (9=9am, 23=11pm), it doesn't matter. From these you can calculate the duration of a class. The work a teacher performs is the sum of the duration of his classes:

select
    t.TeacherID,
    sum(c.EndTime - c.StartTime)
from Teacher as t
inner join Class as c
    on c.TeacherID = t.TeacherID
group by
    t.TeacherID

(Sorry if that doesn't parse - I work with SQL Server and am not familiar with MySQL syntax. Hopefully you get the idea.)

To cope with schedule conflicts add a day-of-week column to Class. Enforce the rule about a teacher being in only one class at a time through triggers or a unique index. Since your schedules are weekly the combination of day-of-week and start time would be a sufficient primary key.

As it is likely your school will offer the same subject more than once, you may find it beneficial to separate the concept of a class (Tuesday at 10am) from an actual instance (5th of March at 10am) to the delivery of that instance (Dave taught on 5th March at 10am). A separate table for each of these would be best.