In Part 1 of this series, I introduced the basic concepts of relational data and how it mirrors Things and Processes in the real world. In this article I am going to look further at relating information - in particular Many to Many relationships.
Recap
We've explored how database design reflects the real world and in particular, began creating a contact database with Companies and Contacts which are related to each other (each Contact can be within one Company and each Company can have many Contacts - a classic One to Many relationship).
[Those following my posts will have all seen 'Sending Bulk Emails in Ninox' which builds on this database further]
When One to Many Won't Do…
Keeping this simple (to make the explanations clearer), let's assume we need to know which of our Products are used by each of our customers?
Some databases will allow the creation of Many to Many relationships - see screenshots from Airtable and Knack:
Airtable:
📷📷
First you have the definition of the link field and the right you can see two linked products in the data.
Knack
📷📷
Similarly, with Knack we have a Many to Many field defined - and an edit form showing two linked records.
At this point I can almost hear readers thinking - "OK - so what's the issue?" - and if the real world were this simple then I'd agree. However, whenever I've used this type of structure in my databases I've almost always had to change it down the line.
One of the usual reasons for this is simple - I usually find that I need more information about the related information - for example, when did they acquire the product or how many of them do they have.
For this reason, I would strongly suggest that you should NOT use built in Many to Many relationships in products like Knack or Airtable (which are both fine products by the way) but rather use the 'correct' method which is to create a Junction Table (or Join Table) which effectively uses back to back One to Many relationships to create a Many to Many one.
So, the full 'spec' for our requirement would read:
"We need to record products used by our Customers (Companies), when they were purchased and how many they bought".
Implementing this in Ninox (or other Database)
In order to add these features to our existing Contact database, we will need two new tables:
Products - with Product Name, Price and Photo Fields, for example
Company Products - with references to Companies and Products, Purchased Date and Quantity fields
Here are the table definitions:
Products
📷
Note the Icon - I thought we could sell guitars!
Company Products
📷
Note - I would generally hide Junction tables so that access to the data would be via either the Product or Company views:
📷
📷
And if you open the Company Products record from either page you'll see:
📷
So, now we've built a Many to Many relationship into our database - but what about other 'joins' that you may have heard about (Inner and Outer joins etc)? These mainly relate to SQL based databases (SQL Server, MySQL, Oracle - and MS Access) and concern joins made in SQL Language and are not that relevant to these platforms.
I do hope these two articles have been useful and that you will try this for yourself - relational databases are not hard once you get the idea.
If you'd like to see the database used in this series, you can download it here.
You can find out more about Ninox, Airtable and Knack with the following links:
As always, comments, questions and discussion welcome!