In this tutorial, we’re going to explore some foundational knowledge about database design that will greatly enhance your app maintainability and flexibility. When first starting with app development, it is very easy to go overboard with table designs and create enormous tables to hold all the data you need. This is analogous to having a gigantic paper form to fill. While all the data might be there, it becomes harder to cross-reference data because there is no way to record such references in those forms. It is better if I illustrate what I mean by giving an example, so for the rest of this brief tutorial, consider a small community library. We want to build a small book cataloguing system. Our first approach might a single table design such as the one below.
While this records all the information we need about a book, the librarian will tell you quite quickly that typing the same author and publisher over and over gets annoying really fast. It is also error prone, very easy to commit a typo when typing Stephen King every time you need to record an entry for one of his 65 novels.
One-to-many relationships
A better approach is having two tables. You record authors in one table and books on another. You change the author key in the books table to be author_id and instead of recording a string with the name of the author, you record the RecordID of that author from the author table. You can do the same for publisher and category, and you’ll end up with tables like this (the publisher and category table are not present on the screenshot below).
Then when you have a form to add a new record in the books table, you can use Dropdown elements for author, publisher, and category, fetching the data from the respective tables.
Example form for adding a new book using dropdowns for the other tables
Binding form fields to table keys
Then set each Dropdown to fetch its value from the appropriate table.
While a setup like this saves you from having to type the same author, publisher, or category multiple times, and it has the added advantage of allowing you to rename them as well once and having the new name applied to all records, it is still not the best solution for a book catalogue.
The shortcomings of this approach become evident as soon as you need to register a new short story anthology or a book with multiple authors. If the author_id field points at a single record in the author table, how can a book have multiple authors?
Many-to-many relationships
The solution is to have a third table that records which book connect to which author. It is a simple table that for each entry records the RecordID for a book and the RecordID for an author. So if a book has two authors, you end up with two records in that table. One with the book RecordID and the first author RecordID, the other with the same book RecordID and the RecordID for the second author.
You might be thinking that this is a much more complex approach (you are correct, it is), and wonder if it is worth it given that cases of books with multiple authors are such few when compared with book from a single author. Consider categories instead. Without a many-to-many relationship (aka a connecting table) you can’t have a book with multiple categories. If book was a murder mystery in a far away planet, you’d have to choose between science fiction and murder mystery, and whatever you ended up choosing would be less than ideal because it wouldn’t really encompass all the book is.
Notice in the screenshot above how we don’t record author, publisher, or category in the books table anymore. That data belongs on separate table such as the Category To Book table shown on the same shot.
Building a user interface to handle such relationship is harder and really depends on your audience and what kind of workflow you want them to have. It can be as simple as having a layout inside the form that lists all the relationships for a given book RecordID, or, having a complete separate screen to record and maintain such relationships. The gist of it is that you need to have the book RecordID before you are able to record who is the author or any of the other fields. Which means that if you want to have a single screen record everything, you’ll need low-code to save the book record first, then save the other fields, and finally save the connections. Below, I’ll show part of that workflow
Be aware that is not the full low-code necessary to accomplish a full record with categories, publisher, and authors. It is just enough to show you how it can go. To make it work, you need to keep a variable author_id_list which holds the authors that were selected for the book you are attempting to save. There needs to be UI and low-code elsewhere to populate that variable with a list of RecordID for each selected author, one per line.
Once you have that list, you can create the book record and keep the book_id (which is the RecordID for the book). Then you can pick the dropdown value for the publisher and create a new record in Publisher To Book table. A book don’t have more than one publisher usually.
After that, loop over each line in the author_id_list variable creating a record in Author To Book table for each of them.
You’d need to have similar code for categories, but that was beyond the needs of this sample since it is the exact same workflow but with different variables.
I know this seems quite complex, but keeping good records is never a simple project. The fact that we can do all of that in one or two pages of low-code should inspire you to be fearless and conquer complexity with Appli. Don’t opt for suboptimal solutions, consider when your data can be recorded with a simple field (one-to-one relationship), or a RecordID to another table (one-to-many relationship), or a connecting table keeping track of multiple RecordIDs (a many-to-many relationship).
While breaking your tables into multiple tables and creating all the low-code and user interface necessary to handle many-to-many relationships might seem a lot of work. The end result for your user is usually a better and more flexible app. Tackling complexity during development will lead to a better product in the end.
