Scheduling Script with PHP/MySQL - Help with Logic

Question

Hello all,

I'm looking to develop a small script where users can insert their "schedule." However, I need some help determining the logic of how to create the DB structure and how to input the "time" of the events in the database.

One thing to note, however, is that when users enter their "schedule", they will not be entering exact dates. Instead, they will be entering 'days of the week.' Somewhat like a 'recurring' appointment.

For example, User A could enter the following time schedule:

MWF - 8:00am - 10:00am MW - 2:00pm - 3:00pm etc...

As you can see, I'm looking for a way to use generic 'days of the week', not necessarily exact dates.

With this in mind, what would be the best way to store this in the database, knowing that eventually, I may be "querying" the database to search for available times.

Should I enter them in milliseconds or seconds? With "0" being 12:00AM on Sunday?

Any suggestions would be great.

Thanks!

Answer 1

First of all, MySQL includes a time data type. I highly recommend you take advantage of it.

Anyway, for your tables (this is for an acadmic scheduling, but the same basic ideas apply):

DaysOfWeek
----------
DowID int
DayOfWeek varchar(10)

Users
------------------------
UserID int autoincrement
UserName varchar(50)
PassHash varbinary(100)
FirstName varchar(50)
LastName varchar(50)
DOB datetime

Semesters
----------------------------
SemesterID int autoincrement
SemesterName varchar(50)
SemesterNumber int
AcademicYear int
CalendarYear int

Classes
-------------------------
ClassID int autoincrement
ClassName varchar(50)
ClassCode varchar(25)

Schedule
----------------------------
ScheduleID int autoincrement
UserID int
SemesterID int
DowID int
ClassID int
BeginTime time
EndTime time

Now, all you'd need to do to see if somebody's available on a Monday between 1 and 2 is:

select
    count(*) as classes
from
    schedule sch
    inner join users u on
        sch.userid = u.userid
    inner join semesters sem on
        sch.semesterid = sem.semesterid
where
    u.username = 'rmcdonald'
    and sem.academicyear = 2009
    and sem.semesternumber = 1
    and sch.dowid = dayofweek($mytime)
    and $mytime between sch.begintime and sch.endtime

Replace the $mytime with whatever time you're checking there.