Relational Databases

Relational Databases are the software implementation of the concepts expressed by the theory introduced by the Relational Model.

In a Relational Database, data is stored in tables.

Each table contains one or more columns. Every column holds data of a specific type, like strings, numbers, and dates.

A row is one record in that table.

Here is a tiny cars table:

Each column has a name and a type. Each row is one car.

You can define that same table in SQL:

CREATE TABLE cars (
  id INTEGER PRIMARY KEY,
  brand TEXT NOT NULL,
  model TEXT NOT NULL
);

INSERT INTO cars (id, brand, model) VALUES
  (1, 'Ford', 'Fiesta'),
  (2, 'Toyota', 'Corolla'),
  (3, 'Ford', 'Focus');

Once the data is in, you ask questions with SELECT:

SELECT brand, model
FROM cars
WHERE brand = 'Ford';

That returns the two Ford rows.

The set of a table and all the rules about its columns is called a schema.

Each table can define constraints upon the data that each row can contain. NOT NULL above is one example. The database rejects a row that breaks those rules.

Primary keys and foreign keys

A primary key uniquely identifies each row in a table. In cars, that is id. No two rows share the same primary key.

Tables can also reference each other with foreign keys. A foreign key in one table points at the primary key of another.

Say we add an owners table, and each car belongs to one owner. Here is a version of cars that links to it:

CREATE TABLE owners (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE cars (
  id INTEGER PRIMARY KEY,
  brand TEXT NOT NULL,
  model TEXT NOT NULL,
  owner_id INTEGER REFERENCES owners(id)
);

cars.owner_id is a foreign key. It must match an existing owners.id, or be empty if you allow that.

Those links let you ask questions across tables, like “all cars owned by Anna”, using joins. Despite the name, “relational” does not refer to these links. A relation is the math term for a table.

A Database Management System (DBMS) is the software that runs the database on a computer.

Commonly, relational databases use the SQL language to create a database, define its tables schema, fill tables with data, and query that data when needed.

Some examples of software implementing relational databases are PostgreSQL, MySQL, SQLite, Oracle and Microsoft SQL Server.

Why pick a relational database?

People reach for relational databases when the data has a clear structure, and you want reliable joins and constraints. SQL makes those queries explicit.

A document store can fit better when each record is a flexible document, and you often load one whole document at a time instead of joining many tables.

Postgres, SQLite, or MySQL?

One useful rule of thumb:

• SQLite when the app and the database live on the same machine, and you want zero server setup (CLI tools, mobile, small sites)
• PostgreSQL when you need a full server, strong SQL features, and room to grow
• MySQL when you already run in an ecosystem built around it, or your host pushes it as the default

For a side-by-side look at limits and features, use the free Postgres vs SQLite vs MySQL comparison tool.

Want me to talk about your product? You can sponsor this site.