How does one code a many-to-many relationship?

Question

I'm getting confused with (mentally) connecting databases and object-oriented programming.

Imagine a teacher's gradebook application, for example, with a UI displaying students in a table's row and assignments in columns. An underlying database might include a many-to-many relationship: a student has many assignments, and an assignment has many students.

How does the underlying code work? Do you have a Student class with a variable referencing a list of assignments? Or an Assignment class with a variable referencing a list of students? Both? ... Do you have some kind of StudentAssignment class linking instances of Student and Assignment? (And, if so, does that mean a class with 50 students and 10 assignments has 500 StudentAssignment objects, 50 Student objects, and 10 Assignment objects in memory simultaneously?!) ... And then do these various classes consist mostly of (for example) SQL statements working with the underlying database?

I know there's a lot of questions here, but they all kind of go together...what's the generally accepted strategy for coding many-to-many relationships?

p.s. Just so you don't think I'm being lazy, I did look at other questions, like a How to model a Many to many-relationship in code? and Modelling a manyToMany relationship with attributes.

Answer 1

In the database you would need a Student Table, an Assignment table, and an intersection table of StudentAssignment. The OOP representation of this could just be each student having a collection of their assignments. Do you need to see what students have a particular assignment? If you do, than by all means populate that information on the Assignment objects. Or, you could also check every student for having a particular assignment.. It's up to you.

Database have limitations in how the data is represented and accessed that you don't necessarily have in an OOP language.

You definitely do NOT need a StudentAssignment object though. That table expresses a relationship, not an entity.

EDIT:

If you needed to go from the assignment to the students who have that assignment, as well as from the student, to all of their assignments, you would need the 50 student objects, and 10 assignment objects. When you create these objects, you would want to populate collections on each of those objects with their associated objects (A Student would have a List Assignments and vice versa).

Let's say you did this:

Create objects for all of your Students(this returns 50 records), setting their Assignments collection to an emty list.

SELECT
    StudentName,
    ID
FROM
    Student

Create objects for all of your Assignments(this returns 10 records), setting their Students collections to an empty list.

SELECT
    AssignmentName,
    ID
FROM
    Assignment

Lets say that you queried for all of the data with something like: (Assuming each student has each assignment, this returns 500 records)

SELECT
    Student.ID [StudentID],
    Assignment.ID [AssignmentID]
FROM 
    Student
    INNER JOIN StudentAssignment ON Student.ID = StudentAssignment.StudentID
    INNER JOIN Assignment ON Assignment.ID = StudentAssignment.AssignmentID

You would want to loop through these 500 records, adding the corresponding objects for each relationship. You would still only have those 60 objects (50 + 10), but their relationships would be defined by the collections of Assignments or Students that each object has.

Answer 2

Your confusion is understandable, translating SQL schemas to OO can often be confusing, as they are different paradigms.

If you consider that OO offers a way to abstract away the underlying implementation, then you will have a clearer path to an answer. Creating an object for every SQL table really buys you nothing in terms of clarity or a better abstraction layer.

Do you have a Student class with a variable referencing a list of assignments? Or an Assignment class with a variable referencing a list of students? Both?

I would likely do both, depending on the data access patterns.

Do you have some kind of StudentAssignment class linking instances of Student and Assignment?

No, this is not needed. Hide this implementation detail in your Student and Assignment classes.

And then do these various classes consist mostly of (for example) SQL statements working with the underlying database?

They can, and often would in real life, but you can also have them call other classes that actually access the data, e.g., using the Repository pattern.