2

Say that I have the following one-to-many relationship:

enter image description here

If I don't want to store any information about the relationship, I can simply put the primary key of School as foreign key in the Student table.

But let's say that I want to store the date on which a student has registered in a school. In this case, can I still put the primary key of School as foreign key in the Student table alongside the date_of_registration attribute? or should I create a new table that contains school_id and student_id as foreign keys alongside the date_of_registration attribute?

If I can use both either approach, what are the advantages and disadvantages of each approach?

|improve this question
  • 2
    student has registered in a school You already answered your self. Register seems a legit entity for me. Anyways, it depends whether you need to keep certain traceability between students and schools. Or students-schools-somethingElse – Laiv Feb 7 '18 at 9:50
1

According to your data model, each student has exactly one school. It makes sense from that point of view to put the relationship meta data on the student, as each student has exactly one of those, too. You can normalize that further to have a relationship table in between, but it is hard to imagine a use case that would necessitate it.

In fact, if it turns out you need more general-purpose data on the student then logically, you would have a 'person' model instead and the 'student' would be the 1-m relationship between person and school.

|improve this answer
  • I agree with this. While you could create a separate table, it would be a one-to-one relationship with Student anyway, and unless there are security concerns, there's no reason why a Student shouldn't also contain information relative to its relationship with School. – Neil Feb 7 '18 at 11:10
0

I would imagine that creating a new/extra table would create extra data to store and extra processing when dealing with queries/updates. If a student can only register once for one school, if they switch school then the table is updated, but it is wholly dependent on the student, when they register at the school.

|improve this answer
0

Your Student entity type is the intersection between School and Person, a many-to-many relationship. As currently documented you have chosen to omit the history of registrations and hold only the current registration. As such Person -- student is 1-to-1 and it is OK to fold Person into Student as you have done. If your problem domain requires history separate Person (name, dob etc.) from Student (registration_date).

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.