'Cause they say you need to do it?
For winnings, see below.
It has to do with standardisation?
Yes.
It’s really necessary?
No, but almost. It’s very difficult to get it right without this table of association, and in some cases unworkable, although possible.
What gets lost doing this?
Need for a JOIN
is the main
And what you get?
Query flexibility, performance, ease of maintenance, consistency, just to mention the main.
This recommendation is only for Mysql?
Not.
Details
A good example is X products suppliers. A supplier most likely provides various products and it is very common for a product to be supplied by multiple suppliers, mainly in wholesale and retail, but also in the industry when the product is commodity or has perfect replacement.
How can you link both? One way is to put in the product itself all the suppliers that can supply it. It may sound weird, but it often works because it’s usually too few suppliers. At least in tables with variable size this is not a big problem. Of course it has disadvantages. The access is not so simple, you may have to do certain contortions to get what you want, know who provides what, may have performance problems, even because it is very given together that it is not necessary to complicate the cache and the readings that will be more frequent.
It gets much worse on the other side. If putting all the products that a supplier provides can be a monster, it is common for suppliers to have thousands of products.
Another solution is to repeat registrations, that is to have a line for each product and supplier that provides it. This hurts the normalization, creates data duplication, can be an even worse situation in terms of performance, memory consumption, and juggling to get to where you want.
The solution that usually works better is to have a mooring table where basically it contains the relation of supplier and product. It is small, with the right indexes is fast access, does not harm the cache, does not load too much, does not duplicate, is not so difficult to query the data thus, allows access from both sides in a simple way in most cases.
Of course it loses a little the locality, it requires some JOIN
in most queries, it is necessary to ensure that the data update is done correctly in this table as well, but not very different from other solutions, at least it is an operation within the relational normal standards that we need.
That goes for any relational database.
Think about how this works on objects in memory. You will have at the supplier a list of the products they provide. And the product will have a list with its suppliers. Are they part of the same object? Contrary to what many people think, it does not, the list is another object. Note that you will probably have two lists. You can do the same in the database, but I see no advantage, it takes work to solve so. It is better a table with only two indexes.
I’m somewhat critical of the use of non-relational databases because the immense majority of the problems we solve with Dbs are relational. When they start using nonrelational technologies to do so they bring problems not found when the technology is relational, then to solve they start to create other technologies and methodologies to fix the inadequate choice of technology.
It doesn’t fit here, but non-relational models tend to be useful in one part of the problem, rarely in the whole problem, so with the evolution of relational Dbs to work better with non-relational data becomes a perfect solution. Even problems that have this feature are often adapted to fit the model, often damaging the user experience, although it can simplify the development a little.
So if that’s the problem, you’ll almost always have to do so, unless the problem can be solved otherwise with peace of mind. Even if you don’t need this mooring table, maybe one day you will and then you will have to make profound changes to the database. Few applications are prepared to work with different physical database structures, which is even a conceptual error, but there I do not know how much pragmatism has to prevail even.
Customer - Request, I believe it is not N-N, or an order will have more than one customer ?
– Rovann Linhalis
Related: What is database normalization?
– rray
Your case of
Cliente
andPedido
be N:N is only in very specific cases where the system requires it. Already the tablesPedido
andProduto
, can have a relationship N:N creating tablePedidoProdutos
.– gato
Without the table, how would you make that relationship?
– bfavaretto
Good examples are real, because invented things like that will have the answer that this is wrong. One of the advantages of relational is to be able to see data in several different ways simply and with performance. This auxiliary table would do it. If you don’t need to access the data like this, you don’t need it.
– Maniero