Simple Database Software: Creating a Relational Database from Scratch
Simple Database Software: The Easy Way to Set Up a SQL Database
Setting up a database often feels like it requires a computer science degree. If you’ve looked at MySQL or PostgreSQL and immediately felt lost, you aren’t alone. Most businesses just need a reliable way to store and manage data and not a complex infrastructure project.
Today, we’re showing you a simpler path: a simple database software that handles the “SQL heavy lifting” for you, so you can focus on your business instead of your backend.
Prerequisites
This blog post is a step-by-step guide on how to create a simple database. Though we do not need to write any SQL to create our database, it is helpful to understand what relational databases are, how they store data, and why they are generally more performant than other ways of storing data.
This guide presumes that readers are familiar with core concepts such as Database Schemas, Entity-Relationship Diagrams, Primary Keys, Foreign Keys, or SQL Data Types.
Last, to follow the steps below, make sure to sign up for free access to Five, a simple database software that lets you create a MySQL database plus a web interface in just a few clicks.
4 Tips For Choosing Your Database
Before we start building our database, a few words about choosing your database.
As mentioned in the introduction to this article, there is a large variety of different database systems, such as MongoDB, PostgreSQL, MySQL, MariaDB, etc. For someone new to databases, this can be pretty overwhelming. So, here are three important things to note:
- PostgreSQL, MySQL, or MariaDB are all database systems, but they are not the tools that you will be using to create a database.
The tools used to create a database are called database GUIs, such as MySQL Workbench, DB Visualizer, DBeaver, or Five. Especially for those new to database development, picking the right tool is just as important as picking the right database technology. - If your use case for a database is a small application used by internal staff, it does not matter so much which database technology you are choosing.
For example, a lot of developers like to argue about the advantages and disadvantages of PostgreSQL and MySQL. But unless you are planning to build the next Facebook or Instagram, these details are almost completely irrelevant. It is hard to go wrong with a plain-vanilla, relational database. Especially for small applications, any relational, SQL database will do. - What matters most is getting your database schema right.
There is no good application without a good database schema. So instead of spending a lot of time delving into databases and reading up on the advantages of NoSQL vs SQL, spend your time on making sure your database schema is correctly set up. This is the single most valuable activity you can spend time on as you build your database. - Choosing an open-source database technology with a large developer community increases maintainability.
In recent years, proprietary databases, such as Airtable or Knack have become more and more popular. While these solutions are usually great for the short-term, problems can emerge in the long-term, as you are trying to expand your database or application.
That’s where open-source databases, such as MySQL are unbeatable. They have an extremely large community of users, and it is always easy to find help. On top of that, by choosing an open-source database technology, your data remains portable and you have less vendor lock-in than with a proprietary product. Last, relational database can be queried in standard SQL, a programming language that any AI tool is incredibly powerful in.
Using Five to Create a SQL Database
Now that we’re ready, let’s set up our first database! We will be using Five to create a MySQL database.
Step 1: Sign Up for Free
Five is a development environment used to rapidly create database-driven business applications. To follow along, make sure to sign up for free access to Five’s online development environment.
Five is a simple database software. It allows users to:
- Create a MySQL database from scratch, including tables and relationships,
- Define database fields from a list of common SQL data types, and
- Automatically manages primary and foreign keys.
Five also comes with a visual database modeler that lets you inspect, edit, and manage your database completely visually. Here is an example of a database developed in Five. As you can see, your database is automatically displayed in an easy-to-read database model that shows every table, its fields and relationships. In addition, primary keys are highlighted.
Step 2: Creating Database Tables
To get started with creating your database, create a new application in Five and then click on Data > Table Wizard.
The Table Wizard requires only two inputs: a table name and database fields.
Table Names in Database Design
SQL naming conventions for tables emphasize consistency, clarity, and avoiding reserved words. While no single standard is universally mandated, most conventions use either snake_case (lowercase with underscores) or PascalCase (capitalized first letters) consistently for table names.
Once you have given your table a name, move on by adding fields to it. This can be done by clicking the Plus button on the far-right side of the screen. You do not need to a primary key to your table – this happens automatically in Five. Instead, only add the fields relevant to storing data.
Last, save your table. You can now go ahead and add more tables to your database. Five also allows for creating table relationships. To do so, click the Next arrow >, and navigate to Relationships. Simply select another table to establish a one-to-many relationship.
For an easier way to follow along, here is a short video explaining the steps to create a table with Five.
Follow the entire video series to learn how to build an entire database-driven web app using Five. For ready-to-use sample database applications, visit https://apps.five.co.
Step 3: Host Your Database Online
Once your database is ready, the next question is usually where to host it. Traditional database GUIs such as MySQL Workbench do not offer hosting services. Instead, you have to move the database to the cloud, picking AWS, Azure or Google Cloud as your hosting platform, for example.
Five, on the other hand, offers a simple single-click deployment feature that lets you deploy your application (and its database) for as little as US$29.99 per month to cloud. This gives you a fully-provisioned cloud-hosted database.
Even better, you can use Five to create an entire application for your database. This application can contain forms, reports, charts, or dashboards.
The front end can also be configured for different user roles with different access rights. Five makes it incredibly simple to assign CRUD permissions to different user roles.
Of course, if you don’t want to host your database with Five, no worries at all. You can also export the database that you have just created with Five as a SQL dump, and then host it elsewhere!
Extensions: Beyond the Database
Once you are done building your database, you can move into Five’s Visual layer. The visual or UI layer lets you add interfaces for your database. This includes forms, charts, dashboards or PDF reports.
More than just a database, Five is an all-in-one tool for building database-driven web applications. To learn more about Five’s features, visit our documentation at https://help.five.org.
Sample Database Applications Developed in Five
If your need for a database is clear and you’ve already figured out how to model your database, then the next question is typically user interface design: how will your users interact with your database? Will they have raw database access, querying the database through SQL? Or will there be a more user-friendly interface that grants users permissions to access or amend data?
The latter, i.e. a proper user interface, is preferable for multiple reasons: it is easier to operate, and it allows for more granular access rights, supporting Role-Based Access Control (RBAC).
For examples of database applications developed in Five, visit https://apps.five.co. Our comprehensive library of template applications contains a library database, fleet management tool and much more. And if you like the look-and-feel of these applications – great! Building applications like the ones shown on our template website is very simple and fast in Five.
Summary: Simple Database Software
Let’s face it: the reality is that the question is not so much if your business needs a database, but rather when to get started with databases.
As explained above, setting up a database can be accomplished by using more traditional database GUIs, such as MySQL Workbench or DBeaver. However, these tools are usually not very user-friendly and require additional tooling to create a database front-end.
For a more comprehensive tool that allows for database creation, database hosting and user interface design, including login protection, consider Five, a simple database software that uses MySQL as its backend. Our free trial lets you explore Five at no cost.
