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.
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.
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.
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.
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.