What is Relational Database?

Tables, rows and keys

In the relational model each table describes one kind of thing: customers in one table, orders in another. The column that uniquely identifies each row is the primary key. When a column in one table refers to the primary key of another, it creates a relationship, and that column is a foreign key.

CREATE TABLE customers (
  id     INTEGER PRIMARY KEY,
  name   TEXT NOT NULL,
  email  TEXT NOT NULL UNIQUE,
  city   TEXT
);

CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers(id),
  placed_at    TEXT NOT NULL,
  total        NUMERIC NOT NULL CHECK (total >= 0)
);

Those constraints let the database refuse bad data at write time: no second customer with the same email, no order for a customer who does not exist, no negative totals. Rules that live in the database, rather than only in application code, hold even when several services and ad-hoc scripts write to the same data.

Normalization: store each fact once

Copying the customer's name and city into every order row looks convenient until the customer moves house. Now hundreds of rows need updating, and missing one leaves two different cities on record for the same person. Normalization is the process of splitting data into tables so that facts are not repeated. First normal form requires one value per cell; second and third normal forms require every column to depend on its own table's key, and on nothing else indirectly.

Treat normalization as the default starting point rather than an absolute goal. Reporting tables that must be read fast can deliberately duplicate data (denormalization). The point is that duplication should be a reasoned choice, not an accident.

Joins bring the pieces back together

Data that has been split apart is recombined at query time:

SELECT c.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Manchester'
GROUP BY c.id, c.name
ORDER BY revenue DESC;

An INNER JOIN returns only rows with a match in both tables; the LEFT JOIN above also lists customers with no orders, showing zero. On large tables, these queries stay fast only if the join and filter columns have a suitable index. The query language itself is covered under SQL.

SQLite: a relational database without a server

Mention relational databases and most people picture PostgreSQL or MySQL running on a server. SQLite takes another route: there is no server process, it is a library embedded in the application, and the whole database is a single file. By the project's own estimate, every smartphone holds hundreds of SQLite databases and well over a trillion are in use; browsers, mobile apps and desktop software commonly store their data this way.

SQLite is a strong choice for mobile and desktop apps, tests, prototypes and read-heavy small to medium websites. Its limit is concurrent writing: only one write transaction runs at a time. WAL mode lets readers carry on during a write, but systems with many clients writing constantly, or several servers sharing one database, are better served by a client-server engine.

Relational or NoSQL?

The strengths of relational systems are schema-enforced structure and transactions that apply several changes as one unit: in a money transfer, the debit and the credit are either both written or neither is. NoSQL systems use document, key-value or wide-column models and focus on flexible shapes and horizontal scaling. The line has blurred: PostgreSQL can index and query JSON documents, and many NoSQL products have added transactions. For most business applications, a well-designed relational schema remains the right place to start.