Students, Lessons, Classes problem

[fugifox]

I've been designing a database for a private school.
Every Student attends some Lessons in specific Classes.

To my knowledge I can meet the above requirement by using a join table
which holds StudentID, LessonID and ClassID,
or to have a join table StudentsandLessons which holds StudentID and LessonID
and another table with a one-to-one relation with StudentsandLessons which holds the ClassID for every Student-Lesson record.

The first way has the disadvantage of not allowing null values for records.
This is a problem for me, because in the school the want firstly to enter Lessons
and afterwards - when Schedule is ready to - enter Classes.
I thought a solution to the above restriction could be the Classes field to have a default value e.g. NoClassYet but this isn't aesthetically correct for me.
The second way overcomes the above restriction, but it doesn't seem "programmatically " correct to me.

Any ideas would be far more than just helpful.
Thank you in advance

 

[gemma-the-husky]

The first method would be right, and there doesn't seem to be any probelm with the nulls. A one-to one table is never necessary I think, unless you have to do this to manage table sizes, or data access restrictions.

Presumably either a lesson has many classes or a class has many lessons? I am not sure which way round this is. If its many to many then you need another table!

However, students can be members of classes (say), or a new student may not yet be allocated to any classes. There are no problems with nills that I can see. Where do you envisage the nulls being an issue?

 

[fugifox]

As I said I have a join table which contains the
Students/Lessons/Classes-IDs.
Then I have a form "Students" which contains a subform
LessonsandClasses.
Every Student picks some Lessons and every Lesson is held in one or more Classes (Lessons and Classes it's a many to many relationship)
So in the subform user should enter the Lessons which Student picked and in which Student will attend it.
E.g.
Student Sara
Maths B1
Physics C2
Biology B2

Student Mary
Maths B4
Physics C2
Economics B2

If I try to enter values in this subform (its source is the joined table)
for Lessons, leaving the Classes fields empty, I get an error from Access
that a value for Classes field is required.

 

[gemma-the-husky]

You shouldn't really have a 3 way table. Tables should be designed to give you a series of one to many relationships between two tables.

Take your many to many classes/lessons position.

If it is a many to many join, then this implies that each class can have multiple lessons, which makes sense, but also that each lesson can comprise several classes which doesn't make sense to me, but might be the case.

So what you should have is

a) a classes table, dealing
b) a lessons table, which may actually link to the classes table, but if not
then
c) a class-lessons table identifying which lessons are for which classs.

The students are then linked to the class only (probably, but it depends), and then you can easily trace through the class-lessons to find which lessons they are supposed to attend.

To put it another way, if you are getting null values, there is likely to be something wrong with the design.

 

[The_Doc_Man]

This problem has been addressed in the forum many times. Search for "class schedules" and "class rolls" and other sorts of key phrases.

In general, you need to apply normalization rules to EVERYTHING.

So you need a class table, a student table, a lesson table, etc.

Lessons are children of classes, from your description. However, students are independent entities.

To handle this, you build a junction table that shows that student A took lesson 1, student B took lesson 2, student B took lesson 3, etc.

One record in the junction table shows where 1 student took 1 lesson. Then you do a group-by on students IN THE JUNCTION TABLE to drive your reports. This way, there is no issue with nulls. To keep the lookups right, you might have to do a layered approach - join the JUNCTION table to students first to build a query that holds student info, then join THAT query to the lesson table to get lesson info. (Then, if needed, join THAT query to the class table to get class info.) When you are done, the QUERY has records for every student, every lesson, every class, no nulls. But you MUST start from the junction table.

 

[fugifox]

I am afraid my case is not so simple.
Almost all of the basic joins are many-to-many, and that's where the problem starts (there's no unique Class-Lesson record as it depends also ).
Let me explain in short the private school schedule structure to have a picture of the issue I am dealing with.

There basic Tables of DB are
Students, Lessons, Classes and Professors.
Each Student picks some Lessons to attend.
Every Lesson takes place in one or more Classes
and teached by one or more Professors.
A typical structure of the daily Schedule is as the following.

08:00 ClassA Maths Pr.Johnson
08:00 ClassB Physics Pr.Peterson

09:00 ClassA Maths Pr.Johnson
09:00 ClassB MAths Pr.Smith
09:00 ClassC Geometry Pr.Peterson

and so on.

The best solution I came up with is a join table called "Schedule" containing
ProfessorID
ClassID
LessonID
Day
Hour

this table is joined with one-to-many relationships with
Lessons
Classes
Professors.

So I have the Schedule of my base containing all the info needed about how the Lessons are organized.
Then I use a join table which joins the Schedule with Students
and by this way I know which Student attends which Class-Lesson-Professor.

So far so good.
Problem started when the private school director asked me to
design also a Form where it is declared which Lessons a Student picks,
in order to help them later to make the Schedule to fit all Students.
For this purpose I added a join table Lessons and Students of which purpose and relations are obvious.
Next to each Lesson field of the form has to be a Class field.
User enters the Lessons of Students and when Schedule is ready,
then user enters next to each Lesson the Class.
DB responds (there's much VB code running in background) and fills in
the Schedule of Student table with the correct record.

All these are tested and working well but for the Null problem I addressed in my first post.

I think that now that you have a clearer picture of what I was called to design you can provide me with a more accurate solution.
Thanks again

PS: I apologize myself for not replying earlier but it was due to the lot of work this week.

 

[The_Doc_Man]

Fugifox, I'm trying to be helpful but something I have to be a bit blunt in order to save time. Please accept that what I am going to say will seem a bit abrupt because I am trying to keep my answer short, not because I am trying to be rude. Sometimes with me it is hard to tell the difference.

I repeat, if you correctly base your operations on the junction tables that implement your many-to-many relationships by being the go-between for your students, lessons, classes, or whatever, you have no null problem. If you are getting nulls, you are probably doing left inner join when you need right inner join (or vice versa... or an OUTER join). There WILL BE NO NULLS if you base everything off the junction tables except...

you will have a null problem in the case where you are trying to print something that hasn't been defined YET - because for instance you let a student choose a class title, but then before you assign a lesson schedule for the student, you attempt to print the lesson schedule - which of course doesn't exist. In that case, you get nulls even with a perfect setup because your dataset is incomplete.

You will also have null returns when you overconstrain something to combinations that do not exist or when you underconstrain the situation. You will DEFINITELY see nulls if you are using OUTER joins rather than INNER joins. It is not possible for me to exactly determine where your nulls problem lies because it could equally be due to implementation or design issues. Your explanation is a bit convoluted and hard for me to follow towards the end of your "special report" description.

Up to the point where you described joining a student to a class-lesson-professor combo, I understood what you were saying. I think that in that last query's triad, one of the elements is superfluous. If I am right in the way I think of this, once you have identified class and lesson, professor is no longer a variable and therefore is not really necessary for the problem to be accurately solved. Stated another way, we often use junction tables as pseudo-keys for many-to-many mappings, but if the professor is determined by the class/lesson combo, it does not actually belong in the junction table to which your students join. It would be a derivative of a separate query that joins class/lesson to IDENTIFY the professor assigned, but this is not a student issue and therefore is questionable as to why you would involve a student in that choice. I hope I'm making myself clear here.

As to nulls in some situations, just remember the old programmer's rule... "Access can't tell you what someone has not told it yet." See if it is possible that you are asking for something prematurely or that your joins are of the wrong flavor.