Many-to-Many relationships

[Ed Shaw]

Originally posted on another site by KamalAman – Answered by greg.fenton

Hi,

What is the best way to represent a Many-to-Many relationship between two Business Models and Persist the relationship in the database.

such as: A machine is made up of many parts – a part is used in many machines.

Thanks,
Kamal

0

[Ed Shaw]

Answer by greg.fenton

Hi Kamal,

As with most things in programming and especially in relational databases, the answer to “what is the best way…” ends up being “it depends”. 😕

There are many patterns that are prescribed in database literature on ways to model various relationships, but when it comes right down to it the only way to be sure you have the right approach is to do real-world (or real-world-like) testing of your model. You need to test data shape, volume, physical characteristics of the server, similar client application connectivity, etc.

All the above said, a guiding principle in computer science is to first Just Do It and then optimize later. As Donald Knuth says, premature optimization is the root of all evil.

There are a few cases in the CRM model where we have many-to-many relationships. What we typically do is model an intermediate class that maps to the M-to-M mapping table. So, for example, there are the two classes User and UserGroup that hopefully are obvious as to what they represent. We have then added a third class, UserGroupLink that represents a single instance of the M-to-M relationship of these two classes. You can look at other similar classes by:

navigate to Business Model >> Classes

in the filter text box at the top of the list of Classes, enter “*Link”
Not all of the M-to-M classes are named this way, but the vast majority are. Some M-to-M classes predate the implementation of this naming convention.

Hope this helps,
greg.fenton

1

• This reply was modified 8 years, 9 months ago by Andrew Penley.
• This reply was modified 8 years, 9 months ago by Andrew Penley.

[Ed Shaw]

Thanks,

Using an intermediate class was my initial guess to model a M-to-M relationship, but I wasn’t sure if NexJ Studio has its own special way of dealing with it.

0

[Aravinddokala]

Hi,

Adding to the EdShaw answer, We got encountered similar problem statement(M-to-M) relational model.
The problem statement was:

We have to read table X and persist to table Y based on a condition, the condition was that take each record(row) of table X and read certain set of ‘columns’ and write to table Y over each column data. (means M-to-M relation ship)

The solution statement was:

prepare a model_X(class) to read table X and prepare a model_Y(class) to write to table Y. Declare collection over model_X and model_Y successively and create a Transformation from collection(model_X) to collection(model_Y1), collection(model_Y2) and so on.

There are two tricky parts that nexJ would do it for the problem_statement(M-to-M)
part 1: table Y would persist the data by nexJ underway (using persist service)
part 2: table Y would persist collection of column data that reads over each row of table X (depends on the mapping at transformations)

Final Transformation would look like:
collection(model_X) -> collection(model_Y1)
collection(model_X) -> collection(model_Y2) and so on..

hope, this could help for the specified problem statement 🙂

Cheers,
Aravind

0

[Ed Shaw]

I’m not totally sure, but it looks to me like you are dealing with more of an ETL type of pattern.

The Many to Many pattern described in this question is meant to address ongoing Many to Many Class Associations such as Users to Meetings. i.e. A User can attend many meetings and a meeting can have many users at it. This pattern discusses how to best represent and persist this type of relationship using the NexJ Framework.

I your case it sounds like you are reading from a table (wrapped in a class) and conditionally filtering it’s contents to write out to other tables. It also sounds like you are using integration services to accomplish this. Another way of coding this would be by using an ETL activity in the Persistence Layer. I’ll write some blog posts on this in the future to make the difference more clear.

Thanks for your feedback

0

[Aravinddokala]

Sure.. Thanks!

0

[Author]

Posts