In the previous post, we looked at how we can generate multiple many-to-many relationships for a single table. In this post we are going to be giving you an alternative to many-to-many relationships that will produce the same result.
Let’s take a step back and look at what exactly we get from a many-to-many relationship. If we use the database from the previous post (the translation service) and look at the languagesbysupplier table we can see an interesting thing:
Interesting isn’t the right word here; fundamental would be better. In this table the SupplierID repeats, as does the LanguageID but, crucially, it is not possible to repeat a combination of the SupplierID and LanguageID. So, for example, the first line contains the combination SupplierID 1 and LanguageID 1 and this can never be repeated in this table. This is actually the whole point of multiple primary keys. The combination is the key.
There is another option in order to achieve the same idea and that is to use multi-field indexes.
Please download the database to follow along:
There are some advantages to using indexes over many-to-many relationships. You can link the primary key of the table to another (which isn’t possible with two primary keys) and you can even add more fields to the index! However, if you simply require what a standard many-to-many relationship table can offer, then you don’t need to use them.
