Trying to wrap my brain around table normalization in a 1:many relationship (2 Viewers)

[cricketbird]

I'm using homerooms and students as an "easier to grasp example" for my more complex case...

Business logic: One student can only be in a single homeroom. But, a homeroom can have many students.

I have a form with the homeroom table as the datasource, with a subform listing the associated students. On this form, you should be able to add students to the homeroom (by adding a new record at the bottom of the subform datasheet) and (theoretically) delete students from homerooms (by deleting the record/student from the subform datasheet).

However, I'm having trouble setting up the relationship between these two tables to make the form work as expected.

Attempt A:
(* = primary key)

• Students table (*studentID, studentName)
• Homeroom table (*homeroomID, homeroomName)
• Students-Homerooms linking table (*studentID, *homeroomID)

The problem is that Access allows me to add a single student to more than one homeroom, which should not be allowed.

Attempt B:
Since each student can only belong to one homeroom, I can also just incorporate homeroom directly into the Student table.

• Students table (*studentID, studentName, homeroomID)
• Homeroom table (*homeroomID, homeroomName)

This correctly enforces the "only one homeroom per student" rule. But, when I try to delete a student from a homeroom using my form, it complains that it can't delete the student because it has related records (true!). I do NOT want to delete the student, just remove the homeroom from their record.

Questions

• I'm leaning towards version A - it feels more normalized to me. Do I then just test every entry, perhaps via a "Before Update" event, to see if that student already has a homeroom elsewhere and handle that error at that time?
• I'm currently testing version B and it feels like a complex VBA decision tree on the "Before Update" event to catch all possible types of entry (adding a new student, swapping a student from one homeroom to another, deleting one student, deleting multiple students).
• Both ways feel like I'm missing something obvious and that if I just set up the relationship better a lot of these issues would be handled seamlessly by Access.

Thanks in advance for any insight!

 

[RonPaii]

Add a unique index on student ID and homeroom ID in the link table.

 

[GaP42]

If a student can belong to one and only one homeroom (at any one point in time) then Attempt B is the correct structure. If you need to retain the homeroom a student was allocated to - say from one year to the next - then Attempt A is more appropriate, however you will need at least an indicator of which record in Student-Homeroom table is current for each student.

Taking Attempt B: you have now indicated via that structure that the student record may be allocated 0 or 1 homeroom. Deleting a RECORD is an action that deletes the record of the student - not the homeroom. To DELETE the homeroom (the student is allocated to) requires you to null the value of Homeroom in the Student record, NOT delete the student record.

 

[The_Doc_Man]

RonPaii's comment illustrates that sometimes it isn't the relationship but the constraint that clarifies the situation.

Student: StudentID (PK), Student info (name, address, etc.), HomeroomID (FK)
Homeroom: HomeroomID (PK), Room number, TeacherID (FK to Teacher's table), anything else unique to the room.

To maintain uniqueness, look at what IS and what ISN'T unique - and WHERE that uniqueness applies.

You have a single record for a student, with a unique ID. Ignoring the denormalization that might occur if the student has a sibling in the same homeroom, the homeroom assignment is unique per student so is a potential attribute of the student. Therefore, homeroom stays with the student.

If you erase the FIELD HomeroomID in the Student table (but don't delete the record), you now have a student with no homeroom - but the existence of the homeroom is not affected because it has its own unique table. You can find the "floating" student because you can erase the foreign key field without damage. If you have Relational Integrity turned on, you need to have an extra record in the Homeroom table that says "unassigned" rather than an actual homeroom number. Or some schools make either the library or the gym the default homeroom for a "floater" kid. (That choice is up to you.) You need that when you put "Required" as a constraint for the HomeroomID.

Put a many-to-one relationship with students being the MANY side and homerooms being the ONE side, and HomeroomID has a unique index in the Homeroom table. But in the Student table, HomeroomID is a foreign key and doesn't actually NEED an index. (It can have one but that would be overkill.) And either have a dummy record for "unassigned" or don't require the FK to be populated. And if you DO have an index on the FK of HomeroomID, it CANNOT have a "unique" constraint. And it can have a "required" constraint ONLY if you have a dummy assignment record.

I think I beat that horse to death trying to be clear.

 

[MajP]

If you are keeping a historical record of each year in A approach then you can do something like
Students-Homerooms
--- StudentHomeroomID - (autonumber)
--- StudentID_FK (foreign key to student table)
--- HomeRoomID_FK (foreign key to homeroom table)
--- CalendarYear
Now you create a composite index on student id, home room id, and Calendar year. You cannot add a student to the same home room in a given calendar year, but you could add a student to a different homeroom by mistake. To check if a student is already added to another homeroom in that year your form design should take care of that or your code.

If you switch homerooms in quarters, or semesters you can add another field
--- Semester
And put that in the index as well.

If there is a discipline problems and student has to get moved into another homeroom mid semester then (and you do not need historical record) you can simply change the homeroom assignment, but not add or delete a record.

Since this is a many to many your design can be both ways.
You can have a main form that picks a homeroom and a CY (CY and Semester) and have a subform to show and add students.
Or you can have a main form with a Student and a CY and a subform to add /edit homerooms.

Although you can only have one homeroom per period this is actually a many to many (with restrictions) because of the linking table.

However neither design is more "Normalized" then the other. It depends on what data needs tracking. If you do not have any requirement to keep a record and only need the current HR assignment then B is correct. As stated you need to change the assignment not delete the record.

Imagine in your student table you had a field like Bus Number. You would simply change it. You probably would not need a history of what bus they were on the previous year. So B would work. But for planning purposes you need a history of what buses they were on per year then A is required.