How to Build a Database: Steps, Types and Choosing a System

A database is an organized collection of data that a program can quickly search, change and store. Behind almost every application, from an online shop to an accounting program, there is a database. This guide explains how a database is built step by step, which types exist, which system to choose and how to protect your data from loss.

A good database goes unnoticed: everything is fast and nobody thinks about it. A bad database is noticed every day. The program opens slowly, the same customer exists three times under slightly different names, and the month-end report does not match the stock in the warehouse. The difference is almost always made at the start, during design.

What is a database?

A database is a collection of data organized for quick search and access. Together with a system for administering, organizing and storing that data, it makes up a database system. (About databases on Wikipedia (wikipedia.org))

Two things should be kept apart: the database itself, that is, your data, and the database management system (DBMS), the program that stores and serves that data. MySQL, PostgreSQL and Microsoft SQL Server are such systems, and one system can hold many databases.

Relational databases are the most widespread. They resemble a set of linked Excel sheets: a customers table, a products table, an orders table. The difference is that a database has rules. For example, it will not allow an order for a customer who does not exist, or two customers with the same tax number.

How is a database built?

Writing the tables is only the middle of the job. The whole process looks like this:

1. Requirements analysis. What data we store, who enters it, which reports are needed, how much data and how many users there will be in five years. This is the most important step and the one most often skipped.
2. Data model. Define the things data is stored about (customers, products, orders) and the relationships between them: one customer has many orders, one order has many products. Everything is drawn in a diagram (an ER diagram), because mistakes are easier to spot in a picture than in code.
3. Tables, columns and data types. Each column gets a type: number, text, date. Money uses a decimal type, not a floating-point type, because floating point causes rounding errors.
4. Keys and rules. Every row gets a unique identifier (primary key), relationships between tables are stored with foreign keys, and you decide what is required and what must be unique.
5. Normalization. Each piece of data is stored in one place. A customer's address lives in the customers table, not repeated in every order. When it changes, it changes in one place and nothing gets out of sync.
6. Indexes. Columns that are searched most often get indexes, like the index at the back of a book. Searches become many times faster, but too many indexes slow down writes, so they are chosen carefully.
7. Access rights. Every user and every program sees and changes only what it needs. An application should never run with an administrator account.
8. Testing with realistic data volumes. A database that flies with 100 rows can grind to a halt with a million. That is why it is filled with test data of realistic size before going live.
9. Backups and maintenance, described below.

This is what two related tables look like in SQL:

CREATE TABLE customers (
    id      INT PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    tax_id  CHAR(9) UNIQUE,
    city    VARCHAR(50)
);

CREATE TABLE orders (
    id           INT PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customers(id),
    order_date   DATE NOT NULL,
    amount       DECIMAL(12, 2) NOT NULL
);

UNIQUE prevents two customers with the same tax number, and REFERENCES prevents an order for a customer who does not exist. Read more about the language itself in the SQL entry of the most popular programming languages list.

Where does a database physically live?

A database is stored in files on the server's disk. In Microsoft SQL Server every database has at least two files: the data file and the transaction log. Every change is written to the log before it reaches the data file. That is why the log is essential for recovery: with it, the database returns to a consistent state after a server crash. When the database uses the full recovery model, log backups also let you restore it to an exact moment, for example to one minute before someone deleted data by mistake.

In practice, two rules apply to disk layout:

• The data file should sit on several disks working as one (RAID), able to recover if one disk fails. Here safety matters more than speed.
• The log is written constantly and sequentially, so it needs a fast disk, ideally separate from the data. It must be protected too, because without it there is no point-in-time recovery.

What types of databases exist?

Databases differ in how they store data. Hierarchical and network databases were the predecessors of relational ones and are rarely seen today outside old systems. These are the types used today:

For most business applications and websites a relational database is the right choice. NoSQL databases solve specific problems and usually complement a relational database rather than replace it.

Which database system should you choose?

A large number of database management systems are in use today. These are the ones you will meet most often:

MySQL is the best-known open-source relational database, now owned by Oracle. It is known for speed, reliability and simplicity. It is available on almost every hosting plan and powers WordPress, so it is the natural choice for a website on ordinary hosting. Official site: MySQL (mysql.com).

MariaDB was created in 2009 as a fork of MySQL, when Oracle announced its takeover of Sun, MySQL's owner at the time. It was started by Michael Widenius, one of MySQL's authors, to keep it fully open and community-driven. It is compatible with MySQL, so many hosting plans use it instead. Fun fact: both databases are named after the author's daughters, My and Maria. Official site: MariaDB (mariadb.org).

PostgreSQL is a powerful open-source object-relational database, known for its close adherence to the SQL standard and its extensibility. It handles JSON data well, and with the PostGIS extension geographic data too. It is the choice for complex applications and data analysis when you want a free system without compromises. Official site: PostgreSQL (postgresql.org).

Microsoft SQL Server is Microsoft's relational database, well integrated with .NET, Windows and Microsoft's reporting and analysis tools. It offers scalability and security for business applications, and there is also a free Express edition with a database size limit. If you work with SQL Server, it is also useful to know how to search for text in procedures, functions and triggers. Official site: Microsoft SQL Server (microsoft.com).

SQLite has no server: the whole database is a single file that the application opens directly. It is in every phone and in web browsers, which according to its authors makes it the most widely deployed database in the world. It is the choice for mobile and desktop apps and smaller tools, but not for a website with many simultaneous writes. Official site: SQLite (sqlite.org).

MongoDB is the best-known document database. It stores data as JSON documents, so different records can have different fields. It suits data whose structure changes often, but for business data with many relationships a relational database is usually the better choice. Official site: MongoDB (mongodb.com).

How are backups kept?

To protect data against a system crash, a mistake or a hacker attack, database backups are made. Backups are set to run automatically at regular intervals. With Microsoft SQL Server and a database in the full recovery model, you also need log backups, for example every 15 minutes, otherwise the log grows without limit.

A good rule is 3-2-1: three copies of the data, on two different types of media, one of them at another location. Backups should never be kept only on the same disk as the database. Against ransomware, which encrypts everything it can reach, a copy that cannot be reached from the server helps.

And most importantly: try restoring the database from a backup from time to time. A backup nobody has tried to restore is not a reliable backup, it is a hope.

Maintaining and updating a database

A database is not finished once it is built. As the data grows, queries that used to be fast become slow, so the database needs regular maintenance:

• monitoring slow queries and speeding them up, most often by adding or changing indexes;
• maintaining indexes and statistics, which the system uses to decide how to run a query;
• security updates of the database management system;
• monitoring disk space and archiving old data that is no longer used every day;
• reviewing access rights, especially when people leave the company.

What determines the complexity of a database project?

The amount of work involved in building a database varies widely. The main factors are the complexity of the data model, the volume of data being processed, the number of concurrent users, and specific security and performance requirements. A small database used by a single desktop application and a replicated system serving thousands of concurrent queries are two very different things, even though both are called a database.