Many-to-Many / intermediate table / bridge table / xref table... help

goroth posted on Tuesday, June 14, 2011

I am having a terrible time trying to find information on how to use CSLA in a cross-reference table scenario.
Basically I am looking for examples on how to select, insert, update, and delete using CSLA in a parent – child setup that are tied together by an XRef table.

Example:
I have a Phone table, Person table, and Company table.
There are two XRef tables PersonPhoneXref and CompanyPhoneXref.
In the current scenario I have Person and Company as Parent objects and Phone as a Child object.
In the Child_Insert, Child_Update, and Child_DeleteSelf I am using reflection to see which Parent (Person or Company) called this child so that I can determine which Xref table to update.

Is there a better way?
I almost wonder if it would be better to create a PersonPhone CSLA and CompanyPhone CSLA object and then inherit from the Phone CSLA object… But I can’t even find good examples on how to do that!
Any help would be much appreciated.

RockfordLhotka replied on Tuesday, June 14, 2011

Please understand that CSLA is not an ORM, and I don't intentionally support object models where the objects look like tables.

Instead, I intentionally support object models where the objects meet the needs of specific user scenarios.

ProjectTracker (current version is in svn or with the Using CSLA 4: ASP.NET MVC ebook) includes a database-level M:M relationship. But that sort of relationship almost never exists in any actual user scenario, so the user scenarios (business objects) demonstrate 1:M relationships.

To put this another way: take the doctor-patient relationship. Your doctor has numerous patients. If you ever have any real illness, you'll have numerous doctors. This is a very obvious M:M relationship at the database level.

But I ask you: in what user scenario does a user interact with the M:M data?

• Adding or editing a patient involves the patient record, and at most might display a historical record of doctors for the patient - so 1:M at the object level.
• Adding or editing a doctor involves the doctor record, and again, might display a list of current or historical patients - so 1:M at the object level.
• When a patient comes into the hospital, they'll be associated with an admittance record, and through that record they'll be associated with n doctors - so 1:M at the object level again.

Hopefully you see what I'm saying. It is critical to understand that good object design almost never looks exactly like the database design. The database M:M relationship is the most obvious and clear example of this basic truth, because the object models using that data are almost never M:M. Actually, in my experience, I have yet to build an M:M object model for a real application - users just don't think that way.