Advanced Forms – Part 9: Display Types Hi…
Database Design: Creating Tables
Blog
Database Design – Part 1: Creating 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 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 a database table in Five. Tables are what a MySQL database is made of. Without tables, there is no database. Inside Five we do not need to write complicated CREATE TABLE commands, but can create an entire database in point and click.
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 Tables
To create a table 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.
Important: Do Not Add a Primary Key
Five’s Table Wizard will automatically add a primary key to your table, so do not add a primary key field to your table now. We will show you how to view the primary key in a second. For now, just remember that you do not need to add the Primary Key!
6. Once you have added all your fields, make the appropriate selections for each one of them. Select a Data Type, whether the field is Required, its Size, and its Default Display Type.
7. Last, click the Tick ✔️ Button to save.
Done! We have just created our first database table for Five’s integrated MySQL database.
Explainer: Data Types vs Display Types
It’s important not to confuse display types with data types.
Display types define how a form field is displayed to your end-users in the application. They only become relevant once you start building your user interface for your database.
Data types, on the other hand, are on the database level and define how the database stores the data. The data type is a guideline for your SQL database to understand what type of data is expected inside of each database column, and it also identifies how SQL will interact with the stored data. In Five, data types are defined on the table level.
Five supports all common SQL data types: text (string), binary (for images), boolean, integer, float, memo, password, etc.
Make sure to select the right data type for your fields, especially if you wish to import data from a CSV file into your database, for example. Data and data type mismatches are very common errors and can be avoided by making sure they are aligned.
Viewing Your Database Table
To view your database table, follow these steps:
1. Click Data > Database Modeler.
Here is what the Database Modeler shows for our customer database sample application.
Note how every table’s first field is its primary key. This primary key was automatically created by Five’s Table Wizard. We did not have to add this key manually.
Five’s Database Modeler is more than just a visual representation of your database. You can make changes to your database table from here. We prefer the Table Wizard, however, because it is even faster.
Database Design: Practical Tips
If you are new to relational database design, the steps we just covered can feel a little bit overwhelming. Luckily, Five follows standard SQL conventions, meaning you can benefit from all kinds of SQL learning resources on the web to learn more about database design. W3 School, for example, is a popular website for learning SQL. Of course, you can also visit our website to learn core database concepts.
In its simplest way, a database is simply a place that stores your data (almost like an Excel spreadsheet), however with many advantages over a flat spreadsheet. First and foremost, databases such as MySQL are relational databases, meaning a database can (and usually does) have more than one table.
Five makes it very easy to create relationships between tables. In our next post, we will learn how to create these table relationships in the Table Wizard.
Finding Help
Have more questions? Join our user community to connect with others and get answers.
