This has always been one of the hardest parts about working with databases...implementing an effective and efficient method of keeping track of schedules (example, an employee's schedule). I've tried several different ideas in the past but each time it always seemed like there had to be a more effective way of accomplishing this. Hopefully some people here may be able to pitch a few ideas.

Basically the idea is to design a database that will keep track of two different kinds of users. One set of users are student tutors who actually work their hours and assist any students who come to the tutoring center during their scheduled hours (or if they have an appointment). The other group of users are staff or office assistants who also have their own relatively fixed schedule but their schedule is not used for the scheduling of appointments with students and such (it would basically just keep track of when they are working).

Before I've always had a sort of schedule table with a field for each day's time in and another field for each day's time out...but having 14 fields (plus a primary key and any other fields necessary) seemed too much.

Are there any other possible ways of accomplishing this?

one possibility:

a users table, containing data specific to a user. Include a user ID field.

A schedules table. UserID, TimeIn, and TimeOut

When retrieving data, group by the userID and the Date, and order by time in.

This way, you aren't limited to any specific number of time blocks in a day. Suppose a user checks in at 8, leaves at 9 for a dentist's appointment, gets back at 11, goes to lunch at 12, returns at 12:30, etc.

Nor are you wasting space.

There will also be various lookup tables (User Type, Charge Codes, etc.) How many and what type would depend on the requirements.

Your idea for the schedules table is what I had in mind for a separate log table, so that I could keep track of everyone's actual work hours (to see who has actually been coming in to work and such). This alone would be helpful if I didn't need a way to check a student-tutor's availability to be assigned to someone coming in and asking for help (i.e. Student A comes in needing help in Calculus and we must find a tutor who teaches calculus at the closest time possible to when that student is available).

Hopefully I explained myself a bit better this time around. Your idea was good and it was actually already part of my plan elsewhere in the database.

Just to prevent any future mixups for any other possible repliers, here is a more thorough breakdown of my database design so far:

USERS: user id, last name, first name, username, password, date of birth, phone,and emais

Users consist of Students, Tutors, Staff, Faculty, Assistants, Administrators, everyone

ROLES: role id, role name, role description

Defines the different roles in the system (as mentioned in the previous table)

USER_ROLES: role_id and user_id as the P/FK's

Assigns the roles to the users

COURSES: course id, course subject (such as HST), course number (such as 101), course name (such as American History I)

Courses are the actual classes that are available for tutoring

TUTOR_COURSES: user id and course id as the P/FK's

This table would keep track of all the courses and which tutors are capable of teaching them

TUTOR_STUDENTS: tutor id (user id of the tutor) and student id (user id of the student) P/FK's

This table keeps track of which students are being tutored by which tutor.

STUDENT_COURSES: user id of the student and course id

Keeps track of what classes a student may need assistance in (Many students have needs in more than one class, such as ENG 101 and MTH 220)

APPOINTMENTS: appointment id, tutor id, student id, appointment_in, appointment_out (datetimes), and course id

Keeps track of all appointments (scheduled future appointments, those that are planned before hand instead of just students dropping in for help)

SESSIONS: session id, tutor id, student id, course id, session in , session out (both datetimes)

This keeps track of all actual sessions. The rules of the tutoring center dictate that a tutor must sign in a student before a tutoring session and then sign them when they are complete so that we can keep an accurate count of students that come in at particular days/times/months and any other reporting needs.

LOG: log id, user id, log in, log out (both datetimes)

Basic time tracking table which will be used whenever a user (of the appropriate role, such as Tutor or Staff) logs in and out of the system. This is the basic time-card system for keeping of track when the tutors and staff are actually present.


The only major table(s) of this database left to design is the table which keeps track of a Tutor or Staffperson's weekly availability. I know there has to be a more elegant way of doing it than trying to brute force all the fields in such as monday in, monday out, tuesday in, tuesday out, etc. Plus, this does not take into account the possibility of some tutors or staff members working multiple shifts in a day (such as coming in and working 9am to 12pm on Monday, but then coming back in the evening after class at 4pm and working until 7pm).

Perhaps I should just create a table such as this:

Schedule ID, User ID, Day, Time In, Time Out

And this would allow for the table to have multiple records for the same day and the same student?

That looka like a very good design!

Yes, the new table seems logical. You might want to write a small procedure/function to determine if a particular instructor is available for a particular time slot.

Ravi

What about adding an insert/update trigger, which checks for overlaps in schedule. If an overlap exists, raise an appropriate error.

Something similar to this solution for an older version of postgreSQL

Yes, that's exactly what my suggestion was about. I've been thinking of writing generic overlap function (package in Oracle) that will accept a table name, the names of the columns, the new values and then perform the check. I don't like the idea of repeating similar code and creating triggers for every table that has a date range and must be non-overlapping. I think it should be possible using database schema triggers; though it might turn out to be quite expensive.

Ravi