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.
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
the developers because they have to figure out how to map their objects
to the tables in the database. Sometime, 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, let's see 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, we 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, we 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 we have mapped an object model to the data model, the next question
to address is how the persistence code should look. First of all, let's see 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();
FillCourseTaken(ct); // Copy
values from the Reader to ct
Student student =
new
Student();
FillStudent(student); // Copy
values from the Reader to student
ct.Student = student; // ct now
references its related (n-1) 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. Although, 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 we 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.
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.
