Database Design: Creating Tables
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 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 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. Instead, we 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 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 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. If you are entirely new to Five and this is your first time using Five, make sure to watch this video to learn how to create an application.
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, table names 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. Add as many fields as required.
Important: Do Not Add a Primary Key
Five’s Table Wizard will automatically add a primary key to your table. Do not add a primary key field to your table manually. 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. If you are dealing with a text field, we recommend a default Size of 100.
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 a 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 each database column. 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 looks like 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 and easier.
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 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 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 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.
Continue Reading About Database Design
Discover all articles of our Database Design series here.
