modeling an object many to many relationship with a transactional bridge table

[Jim Thomason]

I'll use the standard example, since it's actually quite literally the
case that I have now. I have students and classes. Students have many
classes, classes have many students. It's in my database in the
standard way, I have a students table and a classes table, and a
student_class_rlt table to join the two of them with my many-to-many.

But, of course, that's only how my data is stored and I really care
about how its accessed, and that's all through objects sitting on top
of a persistence layer.

Ideally, I'd like to be able to access my classes from a student via a
method:

my @classes = $students->classes;

And I can do that by having the persistence layer map the
student_class_rlt table to a nonsense class ("StudentClassRLT") and
then populating those objects Class attribute and returning it in
their place. Standard stuff.

The issue, as always, is that my join table contains additional
information, start_date and end_date, for instance (when the student
entered and exited that class), so I can't simply throw away that
middle object.

The solution I came up with is to subclass the Classroom class and
wire it up to both the classes and the student_class_rlt table. Then
the ->classes method returns these newly subclassed objects that are
full fledged classrooms with some additional attributes (the user id,
the start date, the end date).

I really like this approach since it encapsulates everything nicely.

my @classes = $student->classes;

foreach my $class (@classes) {
print $class->name, "\n";
print $class->start_date, "\n";
print $class->end_date, "\n";
};

Then I hit a snag - going the other way from classes to students. I
could just subclass students the same way, but then I ended up
duplicating my definition to interface with the student_class_rlt
table into my Student subclass and my Class subclass. Very very bad.

And that's where I stand.

My first question is, does this approach of subclassing on each side
sound like a reasonable one to take, or is there some potential gotcha
that I'm not thinking of?

Secondly (and more important), what would be a good place to stick the
definition of the bridge table?

I certainly don't want to duplicate it in each class, so that's out.

I could stick it into a side class and have them inherit from that as
well as my root object (no, I don't want to hear about the fragile
base class problem), but then I'm introducing multiple inheritance
which I've so far kept out of the system and would like to keep out.

I could stick it in an external class that just has the data for the
table def, and then have the subclasses pull it in from there, but
that module doesn't really exist as anything on its own.

I could stick the table definition into a configuration file. This
seems like the best approach to me, but I'd need to come up with a
place to store it. It also introduces the concept of storing table
information in two different places (either within a class or within a
configuration file), which I don't like; so I may end up re-working my
existing classes to stick their table definitions into configuration
files as well. I've all but talked myself into taking this approach.

Thoughts?

-Jim.....

 

[A. Sinan Unur]

(e-mail address removed) (Jim Thomason) wrote in

I'll use the standard example, since it's actually quite literally the
case that I have now. I have students and classes. Students have many
classes, classes have many students. It's in my database in the
standard way, I have a students table and a classes table, and a
student_class_rlt table to join the two of them with my many-to-many.

But, of course, that's only how my data is stored and I really care
about how its accessed, and that's all through objects sitting on top
of a persistence layer.

I am really fond of Class:BI

http://www.class-dbi.com/cgi-bin/wiki/index.cgi?ComplexManyToMany

might help.

 

[ctcgag]

The issue, as always, is that my join table contains additional
information, start_date and end_date, for instance (when the student
entered and exited that class), so I can't simply throw away that
middle object.

The solution I came up with is to subclass the Classroom class and
wire it up to both the classes and the student_class_rlt table. Then
the ->classes method returns these newly subclassed objects that are
full fledged classrooms with some additional attributes (the user id,
the start date, the end date).

I think that I would just make an "Enrollment" object which contains
a student object, a course object, plus a final grade field, the date
fields, etc.

I really like this approach since it encapsulates everything nicely.

my @classes = $student->classes;

my @courses = map $_->get_course, $student->get_enrollments();

Thoughts?

Yes, I like "course" rather than "class" to refer to those things people
take in school, at least when one is engaged in OOP.

Alas, "course" is also overloaded, but not as badly as "class" is.

Xho

 

[Topmind]

I'll use the standard example, since it's actually quite literally the

case that I have now. I have students and classes. Students have many
classes, classes have many students. It's in my database in the
standard way, I have a students table and a classes table, and a
student_class_rlt table to join the two of them with my many-to-many.

But, of course, that's only how my data is stored and I really care
about how its accessed, and that's all through objects sitting on top
of a persistence layer.

Ideally, I'd like to be able to access my classes from a student via a
method:

my @classes = $students->classes;

And I can do that by having the persistence layer map the
student_class_rlt table to a nonsense class ("StudentClassRLT") and
then populating those objects Class attribute and returning it in
their place. Standard stuff.

The issue, as always, is that my join table contains additional
information, start_date and end_date, for instance (when the student
entered and exited that class), so I can't simply throw away that
middle object.

This sounds like suspicious normalization. Perhaps I am
misinterpreting it. The course
date range should be stored with the course, not with the
many-to-many table. It is not different per student.
I suppose if a student drops out
of a class we may need to store the drop-out date.
However, that may be more info than the system needs.
A "teacher's note" column may be sufficient for that.

You may be interested in this little campus schema
experiment:

http://www.c2.com/cgi/wiki?CampusExample

As far as OO design, I don't believe in OO modeling for
such domains, so I won't comment on them.

-T-