Trying to wrap my brain around table normalization in a 1:many relationship

[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.

 

[mike60smart]

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!

Can you upload a copy of your database?

 

[mike60smart]

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!

It might be a better option to explain your more complex case.

 

[cricketbird]

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.

I do not need to keep any history - only current state. I am also leaning towards attempt B.

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

Hmm...I had not thought of using indexes separately from the primary keys. I will explore this - this may be the solution. Thank you

 

[MajP]

I do not need to keep any history - only current state. I am also leaning towards attempt B.

Hmm...I had not thought of using indexes separately from the primary keys. I will explore this - this may be the solution. Thank you

That is a little confusing because you are leaning towards B, but you think the solution is indexes in A.
If you simply need to make a HR assignment without maintaining history then B is the simpler and more correct solution. Unless there is more to it in the real problem.

If you go with B you should enforce referential integrity between the Student table and the Home room table.
In the Student table the HR ID is a foreign key. In the HR table it is the PK.

However, do not enforce cascade deletes. If you delete a classroom that would delete all the related students. You should be forced to either remove the students for that classroom (set the HR id to null in the student table for that classroom) or reassign all students (pick a new HR ID in the student table)

I am 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).

None of the above should be applicable.
1. When adding a new student you should have a combobox to pick a classroom and save the CR id into your student table.
2. When you swap a student you simply go to that student record and pick a new HR from the pull down
3. I do not know why you are deleting students, but if you do there should be no issue. Although the Student table and the HR table have data integrity enforced the Student table is the child of the HR table. (It is a child since the student table hold the foreign key of HR ID. In the HR table HR ID is the PK). Deleting a student has no effect.

 

[MajP]

OK, I thought about what you are probably trying to do and I get the problem. This is not a table or normalization issue, but likely a GUI issue. This is not the traditional Parent Child model where you create the parents and then create new child records. This is an assignment problem, where you have two set lists and need to assign an existing record (or unassign) to a parent record.


Sure you can go student by student and pick their class rooms, but that is limited and does not provide a lot of visibility of who is assigned to whom.

Instead, you probably want to go to a classroom and see who has been assigned and not yet assigned. You want to be able to un assign them, reassign them and see the changes.

Create the main form as class room.
Create two subforms.
1. Assigned students is linked by HR id
2. Unassigned students is not linked and it is queried to students who HR ID is null

Have combo boxes on both so you can reassign the HR ID

Now you can Scroll the main form to see a Home room and related Students. You can Unassign a student and they show up in the below list. You can pick an HR and they will appear on the correct main form list.


,

 

[Isaac]

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.

Yeah. Your terminology is confusing yourself.
To remove an allocation, you aren't or shouldn't be deleting anything - just updating a record.