Schools DB confusing problem

[fugifox]

I am trying to redesign an older DB for a school,
to meet the new needs that emerged.
I am totally confused due to the complexity of the problem. I've already searched in this forum and in Google but most articles are for simpler DBs.
Well to get to the point.

School has Students,
Students attend to Classes (many to many)
Students are assigned Lessons (many to many)
and coming to the confusing part
a Lesson in specific Class may be teached from two different Professors
(e.g. the 1st may teach the theory and the other the excersizes).
So another many to many Class-Professor and another Class-Professor-Lesson and so on?

After throwing away many sheets of paper I come up with a schema which seems a little bit strange but seems to going to work for my case.
I figure out that I could have a unique junction table storing all these info, and that's no other than the Schedule table.
So I'm thinking of having the following relationship schema:

Students
StundeID
Name
etc.

Lessons
LessonID
Title

Classes
ClassID
Title

Professors
ProfessorID
Name
etc.

Schedule
ScheduleID (maybe a combination of all others)
ProfessorID,join with Ptofessors
ClassID, join with Classes
LessonID, join with Lessons
Day
Time

Schedule_Students
tableID
ScheduleID,join with Schedule
StudentID, join with Students


What is your oppinion about? Can you see any problems which may emerge from such a design?
Thanks in advance

 

[The_Doc_Man]

First, you are absolutely correct: This is a confusing, complex problem.

You definitely need the following:

A student table with Student ID as the prime key (PK).
If the lessons really ARE individually identifiable, then you need a lesson table with a lesson ID.
The teachers/professors have their own table with a teacher ID.
The classes would have their own class ID.

So I will give you the feedback that you started correctly to isolate the basic entities and separate them out. But I have a question regarding classes.

Is a class only taught once per semester or quarter or whatever you are using for a time basis? Class ID might or might not be "atomic" depending on the answer. For instance, if you have a class in European History and you have more students in it than can fit in a single session, you might have to break up the class into two sessions. At which point the classes, having different sessions, might have slightly variant lesson plans - whether the two sessions were taught by the same professor or different professors. I saw it happen more than once during my own college days.

OK, next issue. If lessons are the same for all sessions of the same class name, or if they differ, defines how the junction table has to look. In general, I think you have a problem if you combine too many elements in the junction table.

It is an abstract theory issue but it can mess you up if you aren't careful. See, if you join two tables to show their interactions, that's easy. Let's (for argument's sake) say that you only have one session of any given class ID - or that the class ID implies the session. Either way, it is a simplification.

OK, you have many class IDs and many students - so a junction table for class enrollment makes sense. And it should NOT be mingled with other junctions.

You have many class IDs and many professors - so a junction table to show which class is taught by which professor makes sense.

But you should not try to make the three-way join by making a single junction table. That is, it is WRONG to make entries of class, teacher, and student as a SINGLE junction in an attempt to reduce the two previous junction tables to a single table. Because that says somethng different. It says that a student's schedule cannot be listed without knowing the professor first. And that a professor cannot be assigned without knowing the students first. But in the real world, these two actions are typically independent. That is, you assign a professor to a class and students separately sign up for the class. It is that independence that would drive you to choose two tables as opposed to trying to collapse two tables into one.

Here are some alternate ideas. Think about them and decide on your own whether it makes sense.

If a lesson is part of a class, all you need to know is that anyone taking that class had that lesson. So the lesson list might bear a (class/lesson)=(one/many) relationship. Then to know which lessons a student took you need to know the class and can DERIVE the list of lessons (essentially by association).

OK, complication: If there is more than one instance of the same class then you need to instantiate the sessions. In that case, the lessons are taught in sessions which are child entities of classes.

To know what lessons were taught, it is not necessary to know who taught the class, so there is no need to include the professor in the lessons. If you know who taught the class (or session), again by lookup/transitive relationships, you can know who taught the particular lesson. So your junction tables are trying to do too many many-to-many all at once. If you keep them separate, you will minimize the work required by the computer - and yourself - to keep things where they belong.

Just remember that Access allows you to nest queries. So if there ever DOES come a time when you need some humongous report, think about layering queries to take advantage of the junctions you really do have.

I won't say it is always wrong to have multi-table joins in a single table. It happens. But it is a rare thing to have a single junction for four separate entity tables.

 

[fugifox]

First of all thank you for your time and your quick reply.
Your comments were far more than jsut helpful and gave me food for thought analyzing the complexity of the problem.

Now, considering your suggestions and especially the one saying that the less the number of related tables in a junction table the better manipulation of the DB, I concluded that the optimal design when it comes to table relationships is the one depicted on my first attachment. The Lv caption implies the “junction level”, for example Lv1 is a junction if two tables while Lv2 is a junction of two Lv1 junctions and so on. It serves only for reference.

The primary key of every junction table is the combination of the PKs of the joined tables. Especially, concerning tables of Lv2 and above, fields which appear two or more times are taken only once. For example the PK of table Stud_Less_Class will be StudIDLessIDClassID (not StudIDLessIDStudIDClassID).

Following the above methodology we result in junction tables with unique PKs and what’s more, the values of the joined tables can be fast and easy extracted directly from the PK of the junction table.

However the tradeoff for the above advantages seems to be that the entering of data become complex. DB user should fill much more tables than if the structure with the 4 join tables at a single junction one was followed (mentioned in my first post). Not only should user fill with data both the Stud_Class and the Stud_Less tables but their Lv2 junction Stud_Less_Class as well. And what's more the same process should be followed for the Teachers Lessons and Classes.

One solution may be to avoid having the Teach_Less and Teach_Class
tables, as they don't serve much and joining Teachers Lessons and Classes directly on Teach_Less_Class. That structure is described on the second attachment. At a first glance, I prefer the second one because apart from being simpler, table Stud_Less_Class is isolated from the Schedule.

I would like to hear your recommendations and suggestions about.
And I truly hope not to got you tired with my long post.

Note: I forgot to mention that the tables Days and Time serve as LUTs because the timetable of the Lessons is not predefined while it can differ among Schools.

 

[Simon_MT]

In these siutations, I always look at what I'm trying to acheive, i.e. bottom up the Lesson.

The Lesson in a Class.
The Class involves Students
The Class involves Professors

The Students need to be Scheduled as much as the Professors so I would tend to establish separate relationships between the Student and that of the teaching staff. Therefore the I would have a Schedule for the Students and another for the Professors.

Simon

 

[CraigDolphin]

I hesitate to weigh in but I found the attached illustrations quite hard to understand. Here's how I interpreted Doc_Man's post although I may have not nuanced it properly (see attached diagram).

Classes are composed of one or more 'Sessions'. (ie Class 101 = Session A and Session B because there are too many students for the lecture room)

Sessions begin on a certain date and end on another date. A session might be scheduled for certain days of the week and times of day.

A student attends a session of a class. Sessions are taught by one or more professors over time, but only one professor at a time.

Lessons are given to a session on a date.

You can figure out which professor gave a lesson because you know the date the lesson was given and the session Id. You also know who was teaching that session because you have a teaching schedule that says professor X was teaching that session from date 1 to date 2. As Doc_Man said, you can layer queries to return those results.

Hope I'm not muddying the water.

 

[fugifox]

After studying in detail the attached design of yours I kind of think that's there are unnecessary extra fields for the same info. For example the startTime is stored in 4 different tables. On the other hand I find using a field like the Day/Time to distinguish records a little bit confusing and I'm afraid it's easier then to have duplicate records or make data retrieving slower.

So, let me go back to my second attachment and having that in mind give you a brief description of how the main part of the DB should look like. I believe you will also understand the design in the snapshot I attach after this.
For simplicity sake let us ignore the need of Sessions and use directly the Classes table.

The basic requirements of the DB are:

1. Four main tables
Students
Classes (like B1,B7, G1, G10 etc)
Professors
Lessons (teached in School like 3rd grade Physics , 2nd grade Maths , 3rd grade Maths etc.)

2. Four different forms used for data entry of the above tables.
Students, Classes, Professors, Lessons

3. A form for the overal School's Schedule containing 4 basic fields:
DayTime, Class, Lesson, Professor
where Class, Lesson and Professor fields are comboboxes with the Row Source property set to the relative tables respectively.

4. A Student Profile Form (SPF) containing the following info
- The basic info of Student (like name, date of birth, address etc)
- A subform for both assigning Lessons to Students
and enrolling every Student-Lesson to a Class.
- A subform for the Schedule of the Student. The latter is easily defined by the Schedule table by finding the record containing the respective Lesson-Class-Professor entry.
Note: If there's more than one respective Class-Lesson records in the Schedule table (different Professors or different DateTime), user should have the option to choose the one s/he wishes (maybe from a popup window).

I believe that the designing of DB for 1 and 2 is straightforward. Problems begin when it’s time to enter data on the Schedule table (not to mention the SPF). According to the diagram you suggest, which StartTime field of all should be queried? And how will the info flow to the other ones? I don’t know maybe it’s obvious and I’m the one making things complex around here but the case is that I can’t figure out how.

Anyway, now that you have a clear picture of what requirements I have to meet please provide me with any suggestions or ideas because the deadline for delivering the DB is approaching.

Thank you in advance

 

[CraigDolphin]

For example the startTime is stored in 4 different tables

Not true. The StartTime is stored in exactly one table in the design I provided.

The variations on startdates and enddates may be confusing you.

In the Session table, these refer to the first and last dates of that session. These fields could be eliminated if you like.

In the teaching schedule table, these refer to the first date a professor might teach in a session, and the last date of his stint teaching that session. Thus, if professor X teaches Session A of Class 101 from monday 1/4/07 to 2/4/07, then professor Y teaches from 2/6/07 to 3/5/07, then prof x returns to finish the session (3/6/07 until 8/1/07) there would be three records in that table.

In the SessionLessons table, the date field exists to record which date the lesson was taught to a session of a class. By comparing this to the records in TeachingSchedule, you know who taught the lesson. If Lesson 31B was taught on 3/9/07 you know that it was prof Y who taught the lesson because he is the only professor listed with a starteachingdate <= 3/9/07 and an end teaching date >= 3/9/07

In any case, it's your db so feel free to do your own thing. I was simply trying to provide a graphical interpretation of what I thought Doc_Man was suggesting in case it helped you.

[Edit]After re-reading your last post I also think we may be talking with differing definitions in mind. I had interpreted 'Classes' to be something like "Physics 101", or "Second Grade Mathematics" while lessons are sub-units of that (ie are elements that collectively make up the curriculum for that class) along the lines of "Differential Equations part 1", "Addition Part 2" etc.

 

[fugifox]

In any case, it's your db so feel free to do your own thing. I was simply trying to provide a graphical interpretation of what I thought Doc_Man was suggesting in case it helped you.

Please don't feel like I'm judging you.
I'm only try to make clear as much as possible how the DB should look like and to figure out which should be the optimal design of it, before starti adding queries.
Thanks again for your time

 

[The_Doc_Man]

A couple of design issues to consider, and I'll admit these are generalities.

1. Access never tells you anything you didn't tell it first.

Implication: Figure out what you want to see or want to get out of the DB ... THEN add fields to support the questions you wanted to ask.

2. You can't do it in Access if you can't do it on paper.

Implication: Explore your problem to see how the entities relate. Don't be afraid to draw lots of pictures. (Humans are very visually-oriented animals.) Get the mechanical parts of the operation firmly in your head before trying to implement. Because if you don't understand the problem well enough to write down the rules and diagram the relationships on paper, you'll never tell Access what to do.

3. Normalization is not the end-all and be-all - but it's pretty far up the ladder towards making things work optimally. For MOST (not all) applications of a size suitable for implementation in Access, get to 3rd normal form as a minimum. Higher than that is good if you can do it, but not really mandatory unless your problem really requires it.

4. This problem is suitable for hierarchical interpretation - but it is possibly a 3-D or higher hierarchy because of the large number of classes (entity types) involved. So if you find that a flat representation causes you to have to cross lines when diagramming, switch to an isometric-grid paper. Or use multiple sheets.