Advanced Forms – Part 9: Display Types Hi…
Database Design: Table Relationships
Blog
Database Design – Part 2: Table Relationships
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 fields to a table to creating many-to-many relationships between tables and importing data – we cover everything you need to know to build the perfect database.
In this blog post, we explain how to create relationships between database tables in Five. Five’s integrated database is MySQL, one of the world’s most popular relational databases. 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 the same way you would 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, create table relationships, and import or query data.
Beyond Five’s database features, you can develop an entire web application on top of your database. Five makes it easy to build a modern web app with forms, charts, dashboards, and PDF reports that all connect to your database.
Unlike traditional SQL tools, which only serve as a graphical interface for a database (but are certainly not very user-friendly), Five allows you to build and launch a user-friendly 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 any 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
If you are about to create a database, then knowing Five’s Table Wizard is the first step. Five’s Table Wizard is a point-and-click interface for creating MySQL database tables.
When creating a new database, we often resort to pen and paper to map out our tables, fields, and relationships. Which table should store what data? What are the data types of each field in the table? Are we dealing with strings, floats, integers or booleans? What relationships do I need to create? What about junction tables?
Equipped with such 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 in this blog post.
Using Five’s Table Wizard to Create Relationships
To create relationship between two tables using Five’s Table Wizard, follow these steps:
Prerequisites:
Have at least one (empty) application ready in Five.
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, database tables should always be singular, using PascalCasing, such as CustomerName.
4. Next, click the Plus button on the right side of the screen to add fields to your database, as shown here:
5. Keep clicking the plus button to add more fields to your table. As before, do not add a primary key to your table. This will be done automatically by the Table Wizard.
6. Once you have added all your fields to your table, click the small next > Arrow, shown here:
7. Click the Plus button in the Relationships section.
8. From the dropdown field, select the related table. 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 related table in Five’s integrated MySQL database.
Explainer: Table Relationships
By selecting a table in the Relationships section, Five will create a one-to-many relationship between the tables.
For example, let’s say we have two tables: Gym and Member. We know that one gym can have many members, so we want a one-to-many relationship between the Gym and the Member table.
To do so, we first create our Gym table, as we did in part 1 of database design, i.e. without any relationships. Next, we create the Member table. For the Member table, we select the Gym table as a related table.
This will add the Gym table’s key as a foreign key to the Member table and create the one-to-many relationship that we need!
Viewing Your Database Tables and Relationships
To view your database tables and relationships, follow these steps:
1. Click Data > Database Modeler.
Here is what the Database Modeler shows for our customer database sample application.
Note how the tables are connected by thin lines that indicate the type of relationship. In Five, zoom in on one of these lines. Each line’s ends are either a single line or multiple lines (“crow’s feet”), indicating how the two tables are related to each other.
You can now also see that some tables contain foreign keys. In the image above, look at the relationship between the Categories and Products tables. One category (“electronics) can have many products (“phones”, “TVs”, “laptops”). This is why the Products table contains the CategoriesKey.
Table Relationships: Practical Tips
Almost every database you will ever design will consist of several related tables. Very few applications can do with just a single, flat table. Often, however, we are not dealing with a simple one-to-many relationship, but with many-to-many relationships.
In the example of gyms and members above, we could imagine, for example, that someone is a member of multiple gyms at once: one gym for spinning, another for strength workouts. Our database design above would not be able to accommodate this scenario, as we only created a one-to-many relationship (one gym can have many members. But one member cannot have many gyms).
Let’s learn how to change this in our next blog post on many-to-many relationships.
Finding Help
Have more questions? Join our user community to connect with others and get answers.
