[Chicago-talk] modeling bridge tables

[Jim Thomason]

There are several object relational persistence gizmos available. Most
of them map a single class to a single table, and a single object to a
single row in the table. They also tend to have ways to define
relationships. This object has some of that object and such.

Let's use an example. Say I have two objects, a company and a
building. Companies are objects, they have rows in the companies
table. Buildings are objects, they have rows in the buildings table.

You can even easily model that a company is in a building, just give
the company object a building attribute, which turns into a
building_id column in the table.

In short, modeling 1-1 relationships is easy.

Other companies, like McDonald's for example, have multiple buildings
that only they occupy. This is again easy, you stick a company_id into
the buildings table. Modeling 1-N relationships is easy.

Unfortunately, there's a whole mess of other companies that have
multiple buildings. You can't just stick additional columns into the
two tables now, you need a third table to bridge them. We'll call it
company_building_rlt. It'll hold a company id and a building id.

This is also the appropriate place to stick in relationship related
information, such as the move-in date, the rent, and the number of
employees that work there.

All easily done in the database, but how in the world should I set up
objects to handle that third table?

Using the standard paradigm from above, I'd create a new object
("CompanyBuildingRLT.pm"?) and relate that way. Companies have
CompanyBuildingRLTs, CompanyBuildingRLTs have buildings. But I loathe
this idea, since it doesn't make logical sense to me. A
CompanyBuildingRLT object? What the hell is that?

Other approaches would be to magically replace my CompanyBuildingRLT
objects with buildings when the company requests them. So that:

my @buildings = $company->buildings();

would load the CompanyBuildingRLT objects and map them to the building
objects they reference, then return those. This has two disadvantages.
1) I lose access to the additional data stored within the table (rent,
move-in date, etc.) and 2) I still need to keep the damn
CompanyBuildingRLT objects around, to directly read from as well as
write to the table.

The third option I can think of is to subclass the Building class and
add in all the associated information from CompanyBuildingRLT. So that

my @buildings = $company->buildings();

would actually return a Building::Company object (or whatever) that is
a building object, but also has attributes for rent, move-in date,
etc. This gives me direct access to the table, while also giving me
direct access to the relational table. This has some disadvantages,
though, relating to going in the opposite direction. Presumably, I'd
want to take a building and do this:

my @companies = $building->companies();

In order for that to work, I'd need to subclass in the other direction
(Company::Building (or whatever)) and have that one populate the same
attributes into this company subclass. This ends up with a lot of code
duplication. And having two classes doing similar things seems like
I'm just begging for confusion.

Finally, I can take this subclassing approach and hang them both off
of a new superclass (CompanyBuildingRLT again, bleh), and have that
contain the common code between them. It works, but it makes my
hierarchy deeper, adds more modules, and brings back the damn
CompanyBuildingRLT table again. Most annoyingly, it would introduce
multiple inheritence, which I really don't want to do.


I was really gung-ho about the subclassing solution until I realized
the issue with bidirectional access, and now I'm not so sure.

Anybody have any suggestions for clean/logical ways to interface with
these things?

-Jim....