Many-To-Many Relations and OOD/OOP

av_harris posted on Monday, May 21, 2007

Rocky,

I was trying to grasp how OOD/OOP handles a many-to-many relation and I stumbled on an old posting of yours: http://www.lhotka.net/Article.aspx?id=ff226256-903f-4aee-a921-8b09ef40901b. In the post, you seem to be saying that the intermediate table concept doesn't fit into OOD/OOP.

In the Project Tracker sample, you have the Assignments table that looks to me like an intermediate table. My application will have several many-to-many relations. 1) should I be using an intermediate table?, 2) if yes, I was planning on having several "assignment" tables to handle each relation. Is this going to work?

I know this is more of a generic design question but I wanted to make sure that my design doesn't make life difficult for me in CSLA.

Art

Plowking replied on Tuesday, May 22, 2007

In Rocky's post he states that

"A relational model requires such an intermediate table."

Assuming you are using a relational database, nearly everybody does, then yes you need an intermediate table to represent a many to many relationship. However, your object model should different to your relational model, and you will have to implement some kind of Object Relational Mapping when you build an OO application on a relational database.

Rockys article describes how you can use lazy loading to avoid an infinite loop in your many to many implementation.

So the answer is, you dont need to be afraid to use an intermediate table, at least in your database (in fact you will have to use one). You should not have an intermediate OBJECT however... (use lazy loading techniques discussed)

av_harris replied on Tuesday, May 22, 2007

I see now that he is talking about an "intermediate" object. However, how does this reconcile with the fact that there is an object named Assignement that corresponds to the Assignments table in the Project Tracker demo? Assignments looks like an intermediate table to me.

I thought I was able to follow the logic in the book and understood the need for the object. I just want to be sure that using an assignment object (that corresponds to my intermediate table) is the right way to go.

RockfordLhotka replied on Tuesday, May 22, 2007

The Assignments table IS an intermediate table. You can't avoid that in a relational model.

The Assignment class is a normalization of behavior that would otherwise be duplicated between ResourceAssignment and ProjectResource.

I wrote ProjectResource first, because it fits into the edit-a-project use case. It was originally self-contained.

Later, when implementing the edit-a-resource use case I was working on ResourceAssignment and realized that it duplicated behaviors from ProjectResource.

So I did the correct thing, and refactored (normalized) those behaviors into a third object/class, so both ResourceAssignment and ProjectResource can collaborate with those centralized behaviors.

The Assignment class does not correspond to the Assignments table. It exists purely to normalize behaviors within the object model. Not all M:M data relationships end up with such a class - it depends on your use cases and whether there's behavior to normalize.

av_harris replied on Wednesday, May 23, 2007

Thanks. That clears things up for me. I guess I was calling the Assignment class intermediate because it controls the interaction of Project and Resource. And, I can see where, although, it does utilize Assignments, it doesn't directly correspond to the underlying table.

I have one more question regarding the M:M relationship.

I notice that there are no constraints set in the db to enforce the relation between Projects and Resources. Obviously, this can be enforced in the BO logic. Is this the preferred method? Personally, I've always resisted using the constraints just because I didn't want a redundant process that could make debugging more difficult. What would be the OO best practice here?

RockfordLhotka replied on Wednesday, May 23, 2007

av_harris:

I notice that there are no constraints set in the db to enforce the relation between Projects and Resources. Obviously, this can be enforced in the BO logic. Is this the preferred method? Personally, I've always resisted using the constraints just because I didn't want a redundant process that could make debugging more difficult. What would be the OO best practice here?

Oh, now you've put your foot in it... This is a dogmatic topic Smile [:)]

You'll be hard-pressed to find a DBA willing to not have the constraints in the database.

But as a developer it is nice if they aren't there.

This is why I am a strong supporter of stored procedures. As a developer, I know my database design is sub-optimal (read: is often terrible). Making the database work well is somebody else's problem (see Douglas Adams for a complete description of why SEP fields are a good thing Wink [;)] ).

By using sprocs, I have a very clear abstraction that documents what my app needs to see in terms of a logical view of the database. A database expert can come along after I've defined what I need, and can rework the physical database to make it sing - with little or no impact on the logical database I see.

Obviously I'm being a bit flippant here. But I'm also quite serious. Sprocs provide a powerful abstraction layer that really can allow the developer to get a lot of work done, and a database expert to do their job as well.

av_harris replied on Wednesday, May 23, 2007

Thanks for the advice.

Now I'm not saying you're right or wrong but since you've got the years of experience working with this stuff, I'm going to follow your lead. And, to all those purists out there reading this, let me say that it has always been my experience that a working app is always more desireable than one down for optimization repairs!