Design, tune, operate and secure production databases: normalisation, indexing, backups, replication, access control, monitoring and safe migrations.

Database Administration

## Why it matters

Most databases that cause a DBA pain at 3 a.m. started as one wide spreadsheet-style table. A customer's email is typed into every order row, so when it changes, half the rows get updated and half do not. A product price lives in ten thousand places. Deleting the last order for a customer deletes the only record that the customer existed. These are called **update, insert and delete anomalies**, and normalisation is the design discipline that removes them.

As an administrator you will review schemas written by developers, and you will be asked why a report shows two different addresses for the same customer. Being able to spot a normalisation problem, and to explain the fix, is a core skill.

## The core idea: one fact, one place

Normalisation is about **functional dependencies**: if you know the value of column A, do you always know the value of column B? If `customer_email` always tells you `customer_name`, then name depends on email, and the name should live in a table keyed by the customer, not repeated in every order.

The normal forms are a checklist, applied in order:

- **First normal form (1NF):** every column holds one atomic value, and there are no repeating groups. A column like `products = 'pen, stapler, tape'` or columns `item1`, `item2`, `item3` break 1NF. Each item becomes its own row.
- **Second normal form (2NF):** 1NF, and every non-key column depends on the **whole** primary key. This only matters for composite keys. In an `order_items` table keyed by `(order_id, sku)`, the column `product_name` depends on `sku` alone, so it belongs in `products`.
- **Third normal form (3NF):** 2NF, and no non-key column depends on another non-key column. In an `orders` table, `customer_city` depends on the customer, not on the order, so it moves to `customers`.

A popular summary: every non-key column must depend on "the key, the whole key, and nothing but the key". Boyce-Codd normal form (BCNF) tightens 3NF for some unusual overlapping keys; in day-to-day work, 3NF is the usual target.

## Worked example

Here is a flat export from an online shop:

```text
order_id | order_date | customer_email  | customer_name | customer_city | sku  | product_name | unit_price | quantity
1001     | 2026-03-01 | ana@example.com | Ana Silva     | Porto         | P-10 | Notebook     | 4.50       | 3
1001     | 2026-03-01 | ana@example.com | Ana Silva     | Porto         | P-22 | Gel pen      | 1.20       | 10
1002     | 2026-03-02 | ben@example.com | Ben Okafor    | Leeds         | P-10 | Notebook     | 4.50       | 1
```

Ask "what does each column depend on?":

- `customer_name`, `customer_city` depend on `customer_email`.
- `product_name`, `unit_price` depend on `sku`.
- `order_date` and the customer depend on `order_id`.
- `quantity` depends on the pair `(order_id, sku)`.

Each group becomes a table:

```sql
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name  TEXT NOT NULL,
  city  TEXT NOT NULL
);
CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  unit_price REAL NOT NULL
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  order_date TEXT NOT NULL,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, sku)
);
```

You can fill the new tables straight from the flat one with `INSERT ... SELECT DISTINCT`:

```sql
INSERT INTO customers (email, name, city)
SELECT DISTINCT customer_email, customer_name, customer_city FROM orders_flat;

INSERT INTO orders (order_id, order_date, customer_id)
SELECT DISTINCT f.order_id, f.order_date, c.customer_id
FROM orders_flat f JOIN customers c ON c.email = f.customer_email;
```

Now Ana's city is stored once. Changing it is one `UPDATE` on one row, and a product can exist before anyone orders it.

## A note on prices and history

Real shops change prices. If `unit_price` lives only in `products`, old orders silently change value when the price changes. The usual fix is to store the **price charged** on `order_items` as well. That is not a normalisation mistake: the price at the time of sale is a different fact from today's list price. Good design asks what each column means, not just where duplicates appear.

## Common mistakes

- **Normalising by pattern, not meaning.** Two columns that happen to hold the same text are not always the same fact (list price vs price charged).
- **Using names as keys.** Two customers can share a name. Use a surrogate key (`customer_id`) or a value that is truly unique, such as an email with a `UNIQUE` constraint.
- **Forgetting the composite key.** `order_items` needs `PRIMARY KEY (order_id, sku)` or the same product can appear twice in one order.
- **Over-normalising reporting tables.** Analytics warehouses often **denormalise** on purpose (star schemas) because they are read-heavy and loaded in batches. Normalise the transactional system; denormalise deliberately, and document it, for reporting.

## Quick recap

- Normalisation removes update, insert and delete anomalies by storing each fact once.
- 1NF: atomic values, no repeating groups. 2NF: no partial dependency on a composite key. 3NF: no dependency between non-key columns.
- Find the dependencies, give each group its own table and key, and link the tables with foreign keys.

## Try it

Open the **Do** tab and normalise a flat order export into four tables, then run the check query to prove nothing was lost.


Split a flat, repetitive table into well-keyed tables in third normal form so each fact is stored once.

Normalisation

Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF

## Why it matters

Application code changes every week. The database usually outlives several versions of it, plus scripts, imports and the occasional engineer typing into a console. Constraints and good types are the only rules that **every** writer has to obey. A DBA who models carefully gets fewer "how did this row get here?" incidents and faster queries.

## Start from the workload

Before picking types, ask how the data will be used:

- **OLTP (online transaction processing):** many small reads and writes, such as placing an order or updating a profile. Favour normalised tables, narrow rows, strict constraints and indexes on the lookup paths.
- **OLAP (analytics):** fewer, large read queries that scan and aggregate millions of rows. Favour wide, denormalised fact and dimension tables, often in a separate warehouse, loaded in batches.
- **Write-heavy logs and events:** append-only tables, often partitioned by time so old data can be dropped cheaply.

The same "orders" data can be modelled three ways depending on which of these you serve. Write down the top five queries and the expected row counts before you design.

## Keys

- **Primary key:** every table needs one. It must be unique, not null and stable.
- **Surrogate keys** (`id INTEGER` / `BIGINT` identity, or `UUID`) never change meaning. **Natural keys** (an ISO country code, a SKU) are fine when they are truly unique and stable. Emails are a poor primary key because people change them; keep them as a `UNIQUE` column instead.
- Use `BIGINT` for high-volume tables in PostgreSQL or MySQL: a 32-bit integer runs out at about 2.1 billion, and changing the type of a primary key on a large live table is painful.
- **Foreign keys** make relationships real. Decide what happens on delete: `ON DELETE RESTRICT` (the safe default), `CASCADE` (child rows go too) or `SET NULL`.

## Types

Pick the narrowest type that is always correct:

| Data | Good choice (PostgreSQL) | Avoid |
|---|---|---|
| Money | `NUMERIC(12,2)` or integer cents | `REAL`/`FLOAT` (rounding errors) |
| Moments in time | `TIMESTAMPTZ` | text dates, local time without zone |
| Calendar dates | `DATE` | `TIMESTAMP` with fake midnight |
| Yes/no | `BOOLEAN` | `'Y'`/`'N'` text |
| Free text | `TEXT` (with a `CHECK` on length if needed) | arbitrary `VARCHAR(255)` habits |
| Status values | `TEXT` + `CHECK (status IN (...))`, or an enum/lookup table | unchecked text |

**SQLite is different.** It uses *type affinity*: a column declared `INTEGER` will happily store `'abc'` unless the table is declared `STRICT` (SQLite 3.37+). Foreign keys are only enforced after `PRAGMA foreign_keys = ON;`. That is fine for a teaching sandbox, but it is exactly why server databases are stricter by default.

## Constraints

Constraints are rules the database checks on every write:

```sql
CREATE TABLE members (
  member_id          INTEGER PRIMARY KEY,
  email              TEXT NOT NULL UNIQUE,
  full_name          TEXT NOT NULL,
  plan               TEXT NOT NULL CHECK (plan IN ('basic', 'pro')),
  monthly_fee_cents  INTEGER NOT NULL CHECK (monthly_fee_cents >= 0),
  joined_on          TEXT NOT NULL
);
```

- `NOT NULL` says the value is required. Make it the default habit and allow nulls only when "unknown" is a real state.
- `UNIQUE` stops duplicates (and creates an index).
- `CHECK` encodes simple business rules: allowed values, ranges, `end_date >= start_date`.
- `DEFAULT` gives a sensible value when none is supplied, such as `DEFAULT CURRENT_TIMESTAMP`.

## Worked example: catching bad rows

With the table above, these inserts behave very differently:

```sql
INSERT INTO members (email, full_name, plan, monthly_fee_cents, joined_on)
VALUES ('ana@example.com', 'Ana Silva', 'pro', 1500, '2026-01-10');   -- ok
INSERT INTO members (email, full_name, plan, monthly_fee_cents, joined_on)
VALUES ('ana@example.com', 'Ana S.', 'basic', 500, '2026-02-01');     -- fails: UNIQUE
INSERT INTO members (email, full_name, plan, monthly_fee_cents, joined_on)
VALUES ('eve@example.com', 'Eve Ruiz', 'gold', 900, '2026-02-03');    -- fails: CHECK
```

In SQLite, `INSERT OR IGNORE` skips rows that break `NOT NULL`, `UNIQUE`, `CHECK` or primary key constraints instead of stopping, which makes it easy to see which rows your design rejects. (PostgreSQL has `INSERT ... ON CONFLICT DO NOTHING` for unique conflicts only.)

## Common mistakes

- **Floats for money.** `0.1 + 0.2` is not exactly `0.3` in binary floating point.
- **Everything nullable.** Nulls leak into reports as blank totals and surprise `NOT IN` results.
- **Rules only in the app.** The next import script will not know them.
- **Wide "god" tables** with dozens of rarely used columns; split optional groups into their own table.
- **No plan for growth.** Estimate rows per year; it changes key types and partitioning choices.

## Quick recap

- Design for the workload: OLTP, analytics or append-only logs.
- Stable primary keys, `BIGINT` for big tables, foreign keys with a deliberate delete rule.
- Narrow, correct types: exact numerics for money, time zones for timestamps.
- `NOT NULL`, `UNIQUE` and `CHECK` make the database enforce the rules.

## Try it

In the **Do** tab, write a `CREATE TABLE` whose constraints reject four bad rows from a given batch of inserts.


Choose keys, data types and constraints so the database itself rejects bad data for the workload it serves.

Modelling for workloads

Database Design 40 - Data Types

7 Database Design Mistakes to Avoid (With Solutions)

## Why it matters

"Should we use Postgres or MongoDB?" is one of the questions a DBA hears most. The honest answer is "it depends on the access patterns", and your job is to make that answer concrete. Picking the wrong store does not fail on day one; it fails a year later when the data has grown and the queries the business needs are slow or impossible.

## The main families

**Relational (PostgreSQL, MySQL, SQL Server, Oracle, SQLite).** Data lives in tables with a fixed schema. Joins combine tables at query time, constraints enforce rules, and transactions are ACID across many rows and tables. Relational stores are the default choice for business records: orders, accounts, inventory, billing.

**Document (MongoDB, Couchbase, Firestore).** Data is stored as JSON-like documents. A document can nest related data, for example a blog post with its tags and comments inside it. Schemas are flexible: two documents in one collection can have different fields. Reads that fetch "one thing and everything inside it" are fast because no join is needed. Modern document stores also support indexes, multi-document transactions and schema validation, but cross-collection joins and reporting are less natural than in SQL.

**Key-value (Redis, Amazon DynamoDB in its simplest use, Memcached).** A key maps to a value. Extremely fast lookups by key, often in memory. Great for caches, sessions, rate-limit counters and queues. Poor for ad hoc questions ("which users signed up last week?") unless you design for them up front.

**Wide-column (Apache Cassandra, ScyllaDB, HBase).** Rows are grouped by a partition key and spread across many machines. Built for huge write volumes and predictable queries by key, such as time-series sensor readings. You design one table per query, and joins do not exist.

**Graph (Neo4j, Amazon Neptune).** Nodes and relationships are first-class. Good for questions about connections: "friends of friends who bought this", fraud rings, network dependencies.

## The core idea: model for how you read

Relational design starts from the **data** (normalise it, then query it any way you like). Most NoSQL design starts from the **queries** (store data in the shape the application reads it). That is the trade-off:

| | Relational | Document / key-value / wide-column |
|---|---|---|
| Schema | Fixed, enforced | Flexible, enforced by the app or validation rules |
| Ad hoc queries and reporting | Excellent | Limited to what you designed for |
| Joins | Native | Avoided; data is embedded or duplicated |
| Horizontal scale-out | Possible, more work | Often built in (sharding by key) |
| Multi-row transactions | Standard | Varies by product and configuration |

## Worked example: three workloads

1. **A payments ledger.** Money moves between accounts; every transfer must debit one row and credit another, or neither. Auditors run ad hoc queries. Choose **relational**: ACID transactions and constraints are the point.
2. **A product catalogue** where a laptop has RAM and screen size, a T-shirt has size and colour, and pages load one product with all its attributes. A **document** store fits well, and so does PostgreSQL with a `JSONB` column for the varying attributes plus normal columns for price and stock.
3. **Login sessions and an API rate limiter** that need sub-millisecond lookups and expire automatically. Use a **key-value** store such as Redis, with a time-to-live on each key.

Notice option 2: the line between families is blurry. PostgreSQL's `JSONB` type (with GIN indexes) and MySQL's `JSON` type give you documents inside a relational database. Many teams start with one relational database and add a specialised store only when a measured need appears.

## Questions to ask before choosing

- What are the top queries, and do they look up by key or explore the data?
- Do several records have to change together atomically?
- How big will it get, and how fast does it grow? Does it need to be spread across regions?
- Who will run it at 3 a.m.? Operational familiarity matters as much as features.
- What do reporting and analytics need? A flexible store may still need a pipeline into a warehouse.

## Common mistakes

- **Choosing by hype.** "NoSQL scales" is not a requirement. Most applications fit comfortably on one well-tuned relational server with replicas.
- **"Schemaless" meaning "no schema".** The schema still exists; it just lives in application code, where it is harder to see and enforce.
- **Using a relational database like a key-value store** (one table of `key, json_blob`) and losing its strengths.
- **Forgetting consistency.** Distributed stores often default to eventual consistency for reads; know what your product promises.

## Quick recap

- Relational: fixed schema, joins, ACID, great for business records and reporting.
- Document: nested, flexible records read as a whole. Key-value: fast lookups and caches. Wide-column: huge, key-partitioned writes. Graph: relationships.
- Choose from the access patterns, consistency needs, scale and the team's operational skills, and remember that hybrids such as `JSONB` exist.

## Try it

The **Do** tab has a short quiz that asks you to match workloads to stores.


Compare relational, document, key-value, wide-column and graph stores and choose one for a given workload.

Relational vs document stores

SQL vs. NoSQL: What's the difference?

Which Is Better? SQL vs NoSQL

Normalise tables, choose keys, types and constraints for the workload, and decide when a relational or a document store fits best.