In the previous post, we looked at how we can actually implement a many-to-many relationship in a database in a way that makes it simple and, crucially, logical to enter data. As stated, there is little point in understanding how many-to-many relationships work if you can’t easily enter data but what happens when you are dealing with multiple many-to-many relationships? Read on to find out.
Please download and open 03_dbManyToMany_1.
Let us look at an example where we would require multiple many-to-many relationships. In fact, there are thousands of data models that require more than one many-to-many relationship but we are looking for a situation where a single table itself requires multiple many-to-many relationships.
We are using a translation service as an example of how this data model could work. A company may require certain documentation be translated into a foreign language. Or even a website. So, it is very likely that they will contact a translation service. Although it might seem that a translation service will have multiple on-site translators, in fact, they may not have a single employed translator and instead sub-contract out this work to individuals all around the world. The translation service and the translators both gain flexibility in their work. But how does the translation service keep track of which translator lives where? Or what skills they have? Well, that is where we come in! Let’s take a look at a small model translation database.
Note – Translation isn’t the only service offered (transcription, interpretation, etc.) so a “translator” will be referred to as a supplier (since they are fundamentally supplying a service) from now on. We will also be referring to “the translation service” as Ted’s Translations as that is easier to write (just).
Each supplier that Ted’s Translations wishes to keep track of will have important data about themselves stored in the database. We know where to store data such as FirstName, LastName, Address, etc. (the supplier’s table if you were wondering) but we will also need to store data such as which languages the supplier speaks, which clients he/she has previously worked for, which services they offer, which subjects they have knowledge of and which forms of translation they are accomplished in. So, what do we do? Do we simply create one field in the supplier’s table for each of the above? If you said yes then minus 20 house points! Of course not. The whole point of the database is to store information about suppliers but it has to be searchable! And for that we will need robust tables rather than flimsy memo/long text fields.
And guess what? Each supplier can speak multiple languages and each language can be associated with multiple suppliers. Hooray! We have a many-to-many relationship.
And guess what? Each supplier can work with multiple clients and each client can be associated with multiple suppliers. Hooray! We have another many-to-many relationship!
And guess what? Well we hope you have the idea by now. We will need 5 many-to-many relationships for the same supplier! 5!!!
What would that even look like at the table level…
If you hadn’t already worked it out, the 5 tables with red boxes are the many-to-many relationships. This is a good time to remind ourselves as to why we use extra tables when sometimes it feels like an extra field in the main table would do.
If we take the example of the Languages table above, said table will provide the end-user with the opportunity to pick from existing data when entering extra info about a supplier. They will have a list of languages to select from and associate with that supplier. If, instead, there were an extra field in the supplier’s table called “languages” where the end-user could free-type languages, we would run into a serious problem. Certain users may not be able to spell correctly and so an unsuspecting supplier could find they have been associated with a language no-one has heard of called spinish! Free-typed fields can be searched (great!) but what is the point if not all languages are spelt consistently? There will be missing records at best and maybe even erroneous ones at worst.
So, the moral of the story? It is better to give than to receive. Only joking. The moral is that you need to think very carefully about what fields go in what table. This is 101 stuff but always worth remembering.
So, how can we create a form that best enables us to represent the data we require. Let’s take a look.
Ok, so what have we got here? The controls on the left contain data about a supplier that is taken directly from the supplier’s table. These text-boxes/combo-boxes are all bound to fields in the supplier’s table and are exactly what we would expect in a data-entry form. On the right, we have 5 sub-forms that are representing the 5 link tables we have in the database (see previous image above). In the same way that we linked a single sub-form in the previous post we are simply repeating the exercise 5 times!
We now have a great looking “main” form which is tidy, organised and well-laid out. But crucially, we have kept to the logical structure that the data-model implied.
Take another look at the entity relationship diagram in the previous image and you will see that a complicated looking data model, if well-thought out and logical, may only require a limited number of forms in order for it to work.
