Additional data for a one-to-many relationship

[Monika_Sutherla]

I am creating a base for tracking students through coursework in a program. I have a one-to-many relationship between students and the courses that they have taken. All the available courses are in a separate table. For each student in the program, I have a field called “Courses Completed” and there I pull in, from the available courses table, all that the student has taken.

My question is, if I want to add the grade the student received or each course taken, how can I do that using the one-to-many structure I have set up.

[Alessio_Monino]

hi @Monika_Sutherland,
To obtain what you are looking for with your current structure, you could have an additional column where you could add the grades for all the courses that each student completed, separated by a comma.
I would, however, advise against this approach because it is a workaround and while it might seem beneficial as it keeps your tables number to 2, it is not a very scalable system and prevents you from being able to use data analysis fields like rollups, counts, etc.

I would instead suggest adding a new table called “Course Enrolments” which matches students and courses. Then for each of the records in this table you could have a grade field, a notes field and any other fields you may want to keep track of.

Hope this helps!

Website: alessiomonino.com
Calendly: Calendly - Alessio Monino
Email: alessio.monino@gmail.com

[kuovonne]

Monika_Sutherland:

one-to-many structure

It sounds like you actually have a many-to-many structure. The way of handling this is with a junction table, as Alessio described.