Many-to-many relations (also known as N:M relations) is a way of describing the relationship between two entities/tables where each one could have, or own, or be related to, many items of the other. To clarify this, let’s use the example from WikiPedia, books and authors. An author can write one or more books, and a book can be written by one author or a team of authors.
Now, if we had a 1:N relation instead, for example, if and author can write many books, but a book can be written only by a single author rather than a team of authors, we’d have a simple relationship between books and authors tables. In AppGini, we’d simply create a lookup field in books table to link it to authors as shown below.
A naive approach to many-to-many relations: author1, author2, …
In the AppGini screenshot above, we created a lookup field in the books table to link it to an author. But that’s a single author. What if the book was authored by 2 or 3 authors? A first thought that might jump to our minds in this case is to add 2 more lookups in the books table for author2 and author 3:
But the above approach has several limitations…
First, it’s not scalable. What if we got a new book that has 5, or 10 authors? Yes, that can happen! We’d then have to go back to the project file, add a few more lookup fields, regenerate the app and re-upload it to our web server. Although AppGini makes this process smooth, it’s still a lot of work to add a new book entry. We’d better get our database structure correct at the beginning and keep schema changes to a minimum when users are actually using our app in production environments.
In addition, this makes it harder to perform searches. What is we want to find books wrote by a specific author? We’d then have to look for books whose author1 is x, or author2 is x, or author3 is x … Have 10 author lookup fields in your books table? Good luck with searching through that!
A smarter approach: intermediate (or junction) tables
To avoid the above issues, a much better approach is to create an intermediate table that has a lookup field to books, and another one to authors. This way, if a book has 5 authors, it would have 5 records in the intermediate table, where the book lookup field points to the same book, while the author lookup field points to a different author in each of the 5 records.
Here is how this would look like in AppGini:
Is this a scalable approach? Certainly. Although it’s unlikely to have a book written by 1000 authors, there are other examples where this might be true. Consider a college course taken by thousands of students. A ‘courses_students’ intermediate table can very easily handle this by adding a record for each student enrolled in the course.
Does this make it easier to search? Yep. If you go to the ‘books_authors’ table and search for a specific author, you are searching only one field, the ‘author’ field. And you’d get all books written by that author, whether she wrote it alone, or with other authors.
Furthermore, we could now use the parent/children feature in AppGini so that when we’re viewing an author, we can see a list of books she wrote below the author details, and when we’re viewing a book, we can see the list of authors of that book below the book details.
I hope that helps. Please feel free to post any questions below.