Many-to-Many Relationships in O/R Mapping

If you are developing an object-oriented .NET application that has to talk to a database, you’ll inevitably map objects to a relational model. And, most likely you’ll face situations where many-to-many relationships exist in your database. This article describes how you can handle many-to-many relationships in O/R mapping.

Most .NET applications are object-oriented and at the same time they have to deal with relational databases. This creates a difficulty for developers because they have to figure out how to map their objects to the tables in the database. Sometimes, there is a one to one mapping between an object and a table; and, at other times, one object may consist of data from multiple tables or subset of data from one table.

The most important thing one can achieve in O/R Mapping is to capture the logical data model design in the object model of the application. This makes it really easy to design, develop, and later maintain this object model. We’ll try to follow this rule when mapping many-to-many relationships to objects.

Data Model

So, what does a many-to-many relationship look like in the database? Here is an example:

Here, you can see a many-to-many relationship between t_course and t_student tables via a bridge table called t_course_taken. The bridge table’s primary key consists of two foreign keys coming from each of the corresponding tables. Additionally, the bridge table has additional attributes for the many-to-many relationship itself.

Domain Object Model

First of all, look at how this would be captured in the object model in C#.

public class Course {
   // Some of the private data members
   // ...
   public Course() {}

   // Properties for Course object
   public String     CourseId { get {return _courseId;}
                                set {_courseId = value;}}
   public String     Name { get {return _name;}
                            set {_name = value;}}
   public int        CreditHours { get {return _creditHours;}
                                   set {_creditHours = value;}}

   // 1-n relationship properties
   public ArrayList CourseTakenList { get {return _courseTakenList;}
                                      set {_courseTakenList = value;}}
}

public class CourseTaken {
   // Some of the private data members
   // ...
   public CourseTaken() {}

   // Properties for CourseTaken object
   public String     CourseId { get {return _courseId;}
                                set {_courseId = value;}}
   public long       StudentId { get {return _studentId;}
                                 set {_studentId = value;}}

   public int        Semester { get {return _semester;}
                                set {_semester = value;}}
   public int        AcademicYear { get {return _academicYear;}
                                    set {_academicYear = value;}}
   public float      Grade { get {return _grade;}
                             set {_grade = value;}}

   // n-1 relationship properties
   public Student    Student { get {return _student;}
                               set {_student = value;}}
   public Course     Course { get {return _course;}
                              set {_course = value;}}
}

public class Student {
   // Some of the private data members
   // ...
   public Student() {}

   // Properties for Course object
   public long       StudentId { get {return _studentId;}
                                 set {_studentId = value;}}
   public String     Name { get {return _name;}
                            set {_name = value;}}
   public DateTime   BirthDate { get {return _birthDate;}
                                 set {_birthDate = value;}}

   // 1-1 relationship properties
   public ArrayList  CourseTakenList { get {return _courseTakenList;}
                                       set {_courseTakenList = value;}}
}

As you can see, Course and Student objects both keep a collection of CourseTaken objects. Now, if t_course_taken table did not have any attributes other than the primary key, you could have simply kept a collection of Student objects in Course and a collection of Course objects in Student. However, to have a consistent design, you should always keep a collection of the object mapped to the bridge table. That way, if you decide to add attributes to the bridge table later, you won’t have completely redo your object model and hence your application. You could simply add attributes to the object mapped to the bridge table.

Persistence Code

Now that you have mapped an object model to the data model, the next question to address is how the persistence code should look. First of all, look at the code for loading objects from the database.

public class CourseFactory : DbObject, ICourseFactory {
   // ...
   public CourseFactory() {}

   public void Load (Course course, int depth)
   {
      try
      {
         // Load the Course record from the database.
         _LoadFromDb(course);

         // Now, load all related CourseTaken objects
         ICourseTakenFactory ctf =
            ServiceProvider.getCourseTakenFactory();
         course.CourseTakenList =
            ctf.FindWithStudent(course.CourseId, depth);
      }
      catch (Exception ex) { throw ex; }
   }

}

In the load method of CourseFactory, you see that the Course object is loaded from the database in a normal fashion. I didn’t include the detailed code for this to keep things short. Then, another database call is made through ICourseTakenFactory, called FindWithStudent. This call returns a collection (ArrayList) of CourseTaken objects. And, the interesting thing to note here is that each CourseTaken object also points to its related (n-1) Student object. Please see the code for FindWithStudent below.

public class CourseTakenFactory : DbObject, ICourseTakenFactory {
   // ...
   public CourseTakenFactory() {}

   public ArrayList FindWithStudent (String courseId, int depth)
   {
      try
      {
         String sql = "SELECT course_id, t_course_taken.student_id,
                              semester, academic_year, grade, name,
                              birth_date
                       FROM t_student INNER JOIN t_course_taken
                       ON t_student.student_id =
                          t_course_taken.student_id
                       WHERE course_id = ?"

         ArrayList ctList =  new ArrayList();

         PrepareSql(sql);
         BeginTransaction();
         AddCmdParameter("@courseId", EDataType.eInteger,
                         courseId, EParamDirection.eInput);
         ExecuteReader();
         while (Read())
         {
            CourseTaken ct = new CourseTaken();
            // Copy values from the Reader to ct
            FillCourseTaken(ct);

            // Copy values from the Reader to student
            Student student = new Student();
            FillStudent(student);

            // ct now references its related (n-1) Student
            ct.Student = student;
            ctList.Add(ct);
         }
         ReleaseReader();
         CommitTransaction();
         ReleaseCommand();

         return ctList;
      }
      catch (Exception ex)
      {
         Rollback();
         throw ex;
      }
   }
}

Note in the FindWithStudent method that a single database call is made to fetch a collection of both CourseTaken and Student objects. However, a cleaner design would have been to load all the CourseTaken objects first and then from within each CourseTaken object call the Student object to load itself. But, that would have been much slower performance because you would be making n trips to the database, once for each CourseTaken to find its corresponding Student object. Therefore, this approach has been taken.

Conclusion

Many-to-many relationships are frequently used in the database. However, they are not often mapped correctly in the object model and this leads to a poor object design and application performance. This article attempts to explain how to map many-to-many relationships in your objects in a somewhat efficient manner and at the same time keeping the object-oriented design principles true.

About the Author

Iqbal M. Khan works for Alachisoft, a leading software company providing O/R Mapping and Clustered Object Caching solutions for .NET. You can reach him at iqbal@alachisoft.com or visit Alachisoft at http://www.alachisoft.com/rp.php?dest=/main_index.html.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read