Scheduling people/resources question

[Jon Crain]

I'm working on a database that schedules classes to rooms, times, and faculty. It's use is not as a calendar, but I'd like to be able to have a week view (Mon-Fri) with what is scheduled either by Faculty or by room.

I have a couple of questions. First is my structure correct...

I have a class table and it has the foreign key for the Faculty, as well as a foreign key for a linking table. The linking table has a foreign key for Days (5 records Mon-Fri) and a foreign key for Rooms (set number of rooms). Am I close for structure?

The big question for me is how does time relate to everything? Does it go in the linking table as a field, or as another table, or should it go into the class table?

Along with that, if I want to graphically view a faculty's week schedule, or a room schedule, how do I manipulate the data to show that?

[comment]

If a class has a foreign key for the faculty, then this suggests that a class is ALWAYS taught by the same faculty member. If so, then scheduling is only a matter of setting a room and a time for a particular occurrence of a class, and faculty does not play any role here.

Also, it's not clear why a class would have a foreign key to the linking table (Schedule?), when a class can be scheduled many times.

To build a calendric display, I suggest you take a look at the following threads:

A simple roster:

http://fmforums.com/forum/showtopic.php?tid/163163/

A full calendar:

http://fmforums.com/forum/showtopic.php?tid/176396

[Jon Crain]

I think I didn't explain the database enough. I don't really want a calendar, I simply need a view of one week, either looking at it from a Faculty perspective or a Room perspective.

After thinking through it more yesterday, I've changed my approach. Initially I was trying to build in the option of having one class be able to be in different times and different rooms, therefore a many to many relationship. But for our purpose, I don't think we really need that.

So now the question becomes, is it possible to store the times for a class in the class table and be able to graphically show one week based on a start time and an end time. I would guess this has been asked before, and I simply need a direction to get started.

jon

[comment]

Are you talking about a permanent week schedule? In any case, multiple times for a class should be stored in a related table.

By now I am not sure what kind of view you are looking for. Why don't you sketch out a mockup?

[Jon Crain]

Hmmm. How to explain...

What I'm doing is creating a way for a person on our staff to map out classes for faculty and rooms. Basically it's creating the class lists for a year from now. As far as the week view goes, it just needs to show Mon-Fri 8am to 8pm of a proposed schedule for any given week.

So in the screenshot attached, you can choose a Faculty Member and see the list of classes, and I would like to put those classes onto a schedule like is on the bottom.

I will also create a viewer table for the rooms as well.

Does that help?

I've also included my updated relationship graph for this part of the db.

[comment]

Well, you say you don't want a calendar - but what I see *is* a calendar, plain and simple. To make this kind of display, you need a table of Slots with (at least) 130 records (26 half-hours x 5 days).

[Jon Crain]

It's been awhile on this post, but I'm back looking at how to make this work.

I've gotten the Time Slots into my database, but am having trouble figuring out which way to join them to the data.

First off, the way I have the slots made is half hour increments from 8am to 8pm (so 26 records) with another field for the day, so since I need 5 days I have 130 records with a time and day for each record.

If I try a cartesian join, the times will show up correctly, but I'm not able to view the proper classes for those times.

If I try to join with times and days (i.e. TimeSlots::Day = Class::Day and TimeSlots::Time = Class::Time) I can get the times to show up, but not the blank times, so it's not graphical.

How would I go about putting in the dummy records for the blank time slots? Is it a relationship thing?

Thanks again.

jon

[comment]

There are two relationships involved here: one is from the viewing table (this can be any table) to the Slots table. You want to always display all slots, so this relationship needs to be a Cartesian product. The second relationship is between Slots and Events (i.e. Classes), and it is based on matching the day and the time.

[Søren Dyhr]

How would I go about putting in the dummy records for the blank time slots? Is it a relationship thing?

No If the relation is a carthesian is it sorted, what is needed is the cutting up a portal in 5 chunks, and putting them up next to each other to form a grid, so it's a layout matter. Depending on the solution might you have records linked to each of the slots record ID if required?

--sd

[Jon Crain]

Wonderful! It's working. But, it doesn't seem to be filtering. It just shows all events/classes, as opposed to those assigned to a Faculty member. How would that relationship fit in?

jon

[Jon Crain]

Hold on, I think I have it. I was able to pass the facultyID to the TimeSlot table and use that to link to the class table along with the day and time. Thanks again for the help.

jon