relationship design setup help

[Michael Knauf]

I'm trying to make a database to help me with scheduling teachers and classes, and I'm stuck at the initial database design.

I have 6 periods, a bunch of classes, and a bunch of teachers... in each periods there are multiple classes offered and multiple teachers in each class. What I want to be able to do is have the database generate a list of classes for each period and a list of teachers for each class in such a way that I can create a schedule for the staff. So for each period, there can be multiple classes, each class can have multiple teachers, but each teacher should only be in one class per period.

I thought that 3 tables were necessary,

a periods table (id, period_name)

a class table (id, class_name, teacher)

a teacher table (id, teacher, class)

Clearly I need to relate these tables, perhaps I need to add fields to facilitate the relationships, but I'm a bit lost. I'd like to end up with a layout that presents me with a grid view of periods (on the x-axis), classes (y axis) and in each grid box, a list of teachers for that class at that period.

Does that make sense? Can anyone help?

[Fenton]

The tables you mentioned are really what I call "reference" tables. They contain the "physical" entities (periods are not really physical, there are time, but I can't think of a better word).

There is one other entity which requires a table however, the "participation" of a teacher in a class at a period. That is the line items table where all of the above come together. It has an ID from each of the 3 tables above.

When you say "multiple teachers in each class", do you mean in the same class-period? Or just in the same class, at different periods. If in the same class-period, then you would need 2 records in this table; or possibly another little table that contained the teachers for the class-period (instead).

It's hard to talk about the 4th "participation" table, because I don't really know what to call it. In everyday use we use the word "class" to describe both the reference and the instance, but that's kind of sloppy.

It is also possible (likely) that you would want to have a teachers-classes table, so you could pre-assign a teacher to a class, before assigning a period. It would be a separate table, because a teacher may teach the same "class" several times a day, but only have 1 record in this table.

Here's a small file I made a while ago, with some of this:

ClassesFaculty.fp7.zip

Edited March 27, 2008 by Guest
added file

[Michael Knauf]

a little more background, it's a summer camp, so let's say that in period 1 there's a painting class, and bob, joe, and sue are all teaching the period 1 painting class. which of course means bob, joe, and sue can't be teaching the period 1 woodworking class, but other teachers will.

If I follow your suggestions correctly, than it should be:

Periods Table: (id, period)

Subject Table: (id, subject, period) -- named to remove "class confusion... the subject is woodworking, the class is period one woodworking)

Teacher table (id, name)

Class Table (id, period, subject, teacher)

does that make more sense?

so how do I link the tables to present:

Period 1 | Painting | Woodworking

| Bob, Joe, Sue | Bill, tony

Etc?

[renev]

This is in reference to the class faculty exaample posted by Fenton. I have to develop a similar kind of relationship.

One BOM can have multiple VPNs and one VPN can have multiple MPNs.

I created the relationship similar to class faculty, but Im unable to enter data in VPN portal as well as MPN portal.

Please help.

Attached is the design and I am trying to add data in BOM detail layout.

Component_Database.zip