Planning Script Using PHP / MySQL - Logic Help

Question

I want to develop a small script where users can embed their "schedules". However, I need help in determining the logic for creating the database structure and how to enter the "time" of events in the database.

It should be noted, however, that when users enter their “schedule,” they will not enter exact dates. Instead, they will enter "days of the week." Rather, as a "recurring" appointment.

For example, user A might enter the following schedule:

MWF - 8:00 - 10:00 MW - 14:00 - 15:00, etc.

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

Given this, what would be the best way to store this in a database, knowing that in the end I can "query" the database to search for available times.

Should I enter them in milliseconds or seconds? From "0" at 12:00 on Sunday?

Any suggestions would be great.

Thanks!

Answer 1

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

In any case, for your tables (this is for acadmic planning, 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 need to do to find out if anyone is available on Monday between 1 and 2:

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

$mytime , .