Database Design: Junction Tables
Database Design – Part 3: Junction Tables
Hi and welcome to Five’s Database Design series. This series of blog posts teaches you how to create a MySQL database from scratch in Five. From adding table fields to creating many-to-many relationships and importing data – we cover everything you need to know to build the perfect database.
This blog post explains how to create relationships between database tables in Five. Five lets you develop a MySQL database, one of the world’s most popular relational database management systems (RDBMS). MySQL stores data in related tables.
Understanding how to create these relationships is a critical aspect of database design. Inside Five we do not need to write complicated SQL commands to create relationships, but can do so entirely in point and click using the Table Wizard.
About Five: More Than Just a Database
Five is a rapid application development environment for building database-driven web applications. Every application developed in Five has its own, fully customizable MySQL database.
In Five, you can create your database like in MySQL Workbench, with one important difference. You don’t need to write SQL commands to do so.
Five gives you the power of a database GUI in an easy-to-use and intuitive web application. You can create tables, assign data types, define table relationships, and import or query data.
Beyond its database features, Five lets you develop an entire web application on your database. Five makes it easy to build a modern web app with forms, charts, dashboards, and PDF reports that are all connected to your database.
Unlike traditional SQL tools, which only serve as a graphical user interface for a database (but are usually not very user-friendly), Five allows you to build and launch a web interface connected to a database. This makes Five ideal for rapidly creating internal databases to store data about products, inventory, business partners, to-do lists, members, suppliers, or other data.
To view Five’s web interface for databases, visit our customer database sample application here.
Five’s Database Design Tools: The Table Wizard
Five’s Table Wizard is the quickest and easiest way to create a MySQL database. It is a point-and-click interface for creating MySQL database tables and defining relationships.
When creating a new database, we often start by mapping out our tables, fields, and relationships in pen and paper. Which table should store what data? What is each field’s data type? Are we dealing with strings, floats, integers or booleans? What relationships do I need to create? What about junction tables?
Equipped with a database model or entity-relationship diagram, we can quickly convert our visual database representation into an actual database using Five.
Let’s learn how to use the Table Wizard to create a many-to-many relationship by using a junction table in this blog post.
Using Five’s Table Wizard to Create a Junction Table
To create a junction table or many-to-many relationship between using Five’s Table Wizard, follow these steps:
Prerequisites:
Have at least one application with two existing database tables that are supposed to be connected by a many-to-many relationship ready in Five.
Explainer: Junction Tables and Many-to-Many Relationships
To create a many-to-many relationship, we need to have a total of three tables. For example, let’s say our database stores information about events and participants. Each event can have many participants. Each participant can go to many events. Here is what our Entity-Relationship Diagram should look like:
The EventParticipant table is a junction table that stores information about events and participants, allowing us to keep track of (many) participants at (many) events. For more details on why this table is required, please refer to our blog post on many-to-many relationships here.
Now, let’s create the junction table in Five, using the Table Wizard. As we have already explained how to create tables, we will only explain how to create the junction table.
1. In Five, select your application and click Manage.
2. Now go to Data > Table Wizard.
3. Give your database table a name by filling in the Name* field. Usually, junction tables should always be named after the two tables they are joining, such as EventParticipant.
4. Do not add any fields. Instead, click the small next > Arrow, shown here:
7. Click the Plus button in the Relationships section twice.
8. From the dropdown field, select the first related table. Five will create a one-to-many relationship between the table you are creating and the table you are selecting.
9. Repeat step 8 for the second related table. Again, Five will create a one-to-many relationship between the table you are creating and the table you are selecting.
9. Last, click the Tick ✔️ Button to save.
Done! We have just created our first junction table with a many-to-many relationship in Five’s integrated MySQL database.
Viewing Your Database Tables and Relationships
As before, you can view your database tables and relationships, by clicking on Data > Database Modeler.
Here is what you should see inside the Database Modeler. Your junction table connects your database tables, and we can now successfully associate participants and events in a many-to-many relationship. The EventParticipant table holds both the EventKey and the ParticipantKey as foreign keys, and, as a third field, has its primary key.
Junction Tables and Many-to-Many Relationships: Practical Tips
Now that we have created a many-to-many relationship, you can see the advantages of using Five’s Table Wizard. With the Table Wizard, we can easily create tables, assign data types, and create relationships.
However, to do so, Five needs to be in charge of primary and foreign keys. So as mentioned before: when using Five and the Table Wizard, do not create primary keys. Five handles these for you.
Finding Help
Have more questions? Join our user community to connect with others and get answers.
Continue Reading About Database Design
Discover all articles of our Database Design series here.
