One-to-many associations using an extra table?

3
https://gettingstarted.mendixcloud.com/link/module/49/lecture/380 I’m new to Mendix but I have a lot of experience with relational database applications. In this lecture it shows that the one-to-many OrderLine_Order association is stored as an extra join table, so there are three tables OrderLine, OrderLine_Order and Order. This is not how I would expect the database to be modeled – in a relational database a one-to-many relationship would just have a foreign key in the OrderLine table that references the primary key in the Order table and a join table would only be added when you have a many-to-many relationship. Is the lecture incorrect and if not then can someone explain why Mendix uses a join table when it is not necessary and does this not create performance issues when an entity has many relationships?
asked
5 answers
2

The association tables were indeed introduced to provide flexibility (agility) when modelling. It makes it easier and faster to migrate your database when changing a relationship from 1-1 to 1-N.

Using tables to store association keys is not uncommon. One example is the Data Vault modelling technique which also stores associations in so called link tables. In this case it's done to be able to keep all historical data, even if relationships change.

Many design/architecture decisions in Mendix are based on the fact that we hear from our customers that they value development ease, speed and agility more than ultimate performance. Performance impact can often be compensated with larger/bigger infrastructure.

Having said that, we understand that performance is an important aspect of software, and query performance is one of the aspects we are constantly investing in. Reducing impact of associations tables is part of this.

answered
7

The lecture is correct, all relations between tables are stored in join tables.

This true for 1 to 1, 1 to many and many to many relations.

I don’t know why this choice has been made by Mendix, so maybe someone from Mendix can elaborate on this? But it does allow all relations to be handled in exactly the same way and changing the relations automatically also would be easier if stored as Mendix currently does. It is true that performance issue might arise when a record has many relations, but in the course of quite a number of years and applications I have not encountered this to be an issue.

I hope this answers your question.

answered
4

The lecture is correct.

One reason I can think of:

It is very easy to change a 1 to many into a many to many relationship or vice versa.

No conversions needed.

I have been an Oracle developer for many years, and when switching to Mendix you have to accept that Mendix does not let the database handle as much as you would expect.

answered
2

Same here. No idea why they have made this technical choice which has a lot of downsides to it:
- it decreases performance

- it makes harder to port the database to another database-type

- it makes the database non-standard,

- it makes it harder for regular database engines to optimise SQL's,

- it makes datamodels (slightly) harder to read,

- it makes creating  sql's take more effort.

And the only upside ever mentioned is, like Jan Blok says, easier switching between 1-n and n-n.

Nevertheless, it is the way it is and it is not going to change.

answered
1

It has been said in all previous comments; the only reason I can think of is to make it easier to switch between 1-n and n-n associations. However this also is not without risk. Going from 1-n to n-n shouldn't be an issue. However going back from a n-n to a 1-n association is not as easy as they make it seem. Bear in mind that the n-n data remains in the database. So to avoid database pollution, you'll have to clean up the data prio to going from n-n to 1-n otherwise you can never be sure what data is actually retrieved by the association. Of course if the n-n association has been used as 1-n this is not an issue. 

But what I consider to be the biggest drawback, is that these join tables are taking up a lot of database storage. (See the table size vs. index size of our database below). We don't have a lot of custom/weird indexes, but running a query for table size shows a lot of space is taken up by… join-tables of which most are a 1-n association. As database size more or less determines the environment size and fees involved, from Mendix point of view it sure is a nice business model. 

answered