While the example discussed here is simple, join tables are incredibly powerful and useful no matter how complex your app is. When we use join tables, we allow for greater flexibility, data integrity, and scalability to our data base.
Good news! I'm not going to get into super complex stuff here. There's a few basics to cover and then we'll get right into a real life example.
First, what is a join table? To understand what a join table is, we need to understand what database model Tadabase uses. Tadabase is a relational database, the most common database model. It's a real rockstar ?.
A relational database is simply multiple data tables, consisting of rows and columns, that connect to each other in order to provide a pathway to see data across multiple data tables. Imagine connecting two spreadsheets together so you can access data from both at the same time.
Back to join tables. A join table is a data table that has multiple outgoing connections - connecting multiple data tables to one data table. This will make sense in a minute, hang on.
Here's our real life example. We have an app to manage Event scheduling. We could build this with just two data tables.
- Events
- Staff (one-to-many connection)
We can add an Event record - a Concert in the Park - and then add staff members to the record in our one-to-many connection field. It'll look like this.
This method can work - depending on what actions you want to perform in your app. You'll be able to see details about the event, and the assigned staff.
But - what if we want to add data specific to each staff member? For example, payroll, staff evaluations, or shift times.
Let's use payroll as our example. We can't specify which staff member gets paid a specific amount. Additionally - after staff members have worked multiple events, we won't be able to run any calculations on how much each staff member has earned over a period of time, or by role.
Enter - Join Tables
Let's add a third data table called "Schedule". This data table has a one-to-one connection to both Events and Staff. When we assign staff members to an event, we'll be creating individual records for each assignment. Every record will contain a staff member + an event. It'll look like this.
We now have so much more flexibility. We can add payroll, or staff evaluations directly on the schedule record. Each record is more easily controlled too. We can change specific field values of a record, or delete a record entirely while leaving the other related records intact.
Published by
