# Chapter 1: Foundations & Data Modeling
## 1.0 - Relations & Normalization (The Cafe Layout)
<img src="assets/elephant_cafe_architecture_layout.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
Postgres manages a structured collection of **Facts** about its world.
In the eyes of the engine, every record is a fact:
- "Babu is a Capybara" is a fact.
- "Saffron costs $400 per kg" is a fact.
- "Supplier #5 delivered 50kg of Apples" is a fact.
PostgreSQL gathers facts of the same shape into a **table**. Classical relational theory calls this a relation and models it as a mathematical set of tuples. A practical SQL table can still contain duplicate rows unless a constraint stops them—a small but important gap we revisit in the academic detour.
### The Discipline of Normalization
If we were setting up the Cafe in a simple spreadsheet, we might be tempted to record every detail about an animal in a single, massive row. This is the **Naive Model**.
| id | name | species | diet |
| :-- | :--- | :------- | :-------- |
| 101 | Babu | Capybara | Herbivore |
| 102 | Pip | Capybara | Herbivore |
| 103 | ... | ... | ... |
Storing descriptive strings like "Herbivore" redundantly in every record creates a **data integrity risk**. If you rename the category, you must update every copy; missing even one creates an **Update Anomaly** where the database disagrees with itself.
We avoid this through **normalization**: giving one authoritative fact one home instead of copying it into every dependent row. `animals.species_id` stores a small key, and a **foreign-key constraint** checks that key against a referenced unique key in `species`. It is an ID with a rule, not a physical pointer to another row.
#### The Anatomy of the Link
Observe how a simple integer placeholder spares the engine from redundancy:
**The `species` Ledger (Fact Blueprint)**
| id | name | diet_type |
| :---- | :------- | :---------- |
| **1** | Capybara | Herbivore |
| **2** | Aardvark | Insectivore |
**The `animals` Ledger (Set of Facts)**
| id | name | species_id |
| :-- | :----- | :--------- |
| 101 | Babu | 1 |
| 102 | Pip | 1 |
| 103 | Arthur | 2 |
When Babu walks in, PostgreSQL joins **`species_id`** (1) to `species.id` and reconstructs his profile. The species description has one authoritative home; each animal carries only the matching key. A foreign key keeps non-null child values honest, although it cannot invent business rules we never declared. The Cafe pays for an extra join and escapes a warehouse full of contradictory copies.
> [!TIP]
> The mathematical machinery behind this model—why a relation is a **set of typed tuples**, and why the planner can rearrange SQL so freely—is the subject of **[[Manuscript/01 - Foundations & Data Modeling/1.2 - Relational Model (An academic detour)|1.2 An academic detour]]**.
---
#### The Blueprint of the Cafe
Before a single order can be processed, Postgres must define the schema of its world using **DDL (Data Definition Language)**. These blueprints — `CREATE TABLE` statements — are the strict contracts that every piece of data must conform to.
> [!NOTE]
> You can find the full, literal blueprints for the Elephant Cafe in the **[[scripts/init.sql|Architectural Inventory]]**.
```mermaid
erDiagram
species ||--o{ animals : "classifies"
suppliers ||--o{ supply_deliveries : "delivers"
ingredients ||--o{ supply_deliveries : "received_in"
ingredients ||--|| flavors : "profiles"
animals ||--o{ animal_favorites : "prefers"
ingredients ||--o{ animal_favorites : "preferred_by"
dishes ||--o{ dish_ingredients : "contains"
ingredients ||--o{ dish_ingredients : "used_in"
animals ||--o{ orders : "places"
orders ||--o{ order_items : "includes"
dishes ||--o{ order_items : "ordered_as"
```
---
### The Three Tiers of Truth (Normal Forms)
A schema does not become trustworthy all at once. It gets there by asking three increasingly awkward questions:
1. Is this one value—or several facts hiding in a trench coat?
2. Does this fact depend on the whole key?
3. Does this fact belong in this table at all?
Those are the first three **Normal Forms**. Each one removes a different way for the database to disagree with itself.
| Normal Form | Plain-English Test | Example Violation |
| :--- | :--- | :--- |
| **1NF: One value per attribute** | Does this cell hold one value PostgreSQL understands as that column's type, or did we smuggle several separate facts into a homemade list? | Writing `Herbivore, Omnivore` in a text column when the Cafe needs to treat those diets separately |
| **2NF: The whole key owns the fact** | If several columns identify the row together, does every other fact describe that whole combination—not merely one part of it? | Storing `order_date` in a row identified by `(order_id, dish_id)`, even though the date belongs to the order alone |
The first two keep facts legible and anchored. The third keeps them from wandering into tables where they do not belong.
#### 3NF: The Source of Truth
Our species split answers that third question. Babu's diet **follows from** his species membership; it is a fact about Capybaras, not a separate fact about Babu. **Third Normal Form (3NF)** removes that **transitive chain** from the animal row: `diet_type` belongs in `species`, and Babu carries the key.
Normalization is not academic purity. It is refusing to make the database remember the same truth twice.
> [!NOTE] One value can still be structured
> PostgreSQL arrays are valid values of array types, and they become useful later in the book. The 1NF question is not whether brackets appear; it is whether the attribute represents one value from its declared domain and whether the application can enforce the dependencies it cares about.
---
### 🧪 Manipulation Lab: The Broken Update (Update Anomalies)
Let's leave one copy behind and watch the Cafe contradict itself.
#### Build the Denormalized Animal Registry
Connect to your database and create a denormalized, temporary table representing a naive animal registry where species details are stored directly inside each row:
```sql
CREATE TEMP TABLE naive_animals (
id INT,
name TEXT,
species_name TEXT,
diet_type TEXT
);
INSERT INTO naive_animals (id, name, species_name, diet_type) VALUES
(1, 'Babu', 'Capybara', 'Herbivore'),
(2, 'Pip', 'Capybara', 'Herbivore'),
(3, 'Waffles', 'Capybara', 'Herbivore');
```
#### Miss One Capybara During the Rename
The Cafe's director decides to update the official taxonomy: the category `Capybara` is to be updated to its scientific name, `Hydrochoerus`.
Write an update statement, but simulate a common developer mistake: target the update using specific IDs rather than the logical category, intentionally missing one row:
```sql
-- Updating the taxonomy, but missing Waffles (ID 3)
UPDATE naive_animals
SET species_name = 'Hydrochoerus'
WHERE id IN (1, 2);
```
#### Duplicated Facts Drift into Contradiction
Query the table to inspect the results:
```sql
SELECT * FROM naive_animals;
```
Notice the state of the registry:
```
id | name | species_name | diet_type
----+---------+--------------+-----------
1 | Babu | Hydrochoerus | Herbivore
2 | Pip | Hydrochoerus | Herbivore
3 | Waffles | Capybara | Herbivore
```
#### One Species Row Restores One Truth
The database now disagrees with itself. You have created an **Update Anomaly**. An external application querying for `'Capybara'` will find Waffles, but miss Babu and Pip. An application querying for `'Hydrochoerus'` will miss Waffles entirely.
In our normalized model, the rename belongs to one `species` row. Waffles cannot be forgotten: he carries the species key, not his own copy of its name.
> [!NOTE] Where the link metaphor stops
> `species_id` is a value, not an address. PostgreSQL may move or replace the physical tuple version for the parent row without changing the child's stored key. The constraint relates values; it does not point at a disk location.
---
## 1.1 - SQL (The Waiter's Pad)
<img src="assets/arch_sql_waiter_pad.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
When you interact with Postgres, you send it a command like this:
```sql
SELECT * FROM animals WHERE species_id = 1;
```
Notice what is missing from that command. You do not specify which file to open on the disk. You do not tell the engine what byte offset to seek, or how to iterate through memory buffers.
**SQL (Structured Query Language)** is **declarative**: you describe the *result* you want; the database engine chooses the physical steps.
> [!IMPORTANT]
> **The Declarative Checkpoint**: If you remember one thing about SQL, let it be this: **You describe the 'What,' not the 'How.'**
### The Waiter's Pad
Think of SQL like a **Waiter's Pad**.
You describe what you want to eat. You do not tell the chef which pan to use or how hot the stove should be.
This separation of concerns is the foundation of database performance. Because you only provide a logical request, the engine's **Query Planner** is granted the freedom to find the cheapest physical path. It can choose to scan every page sequentially, or it can use an index to jump directly to the answer.
### The Three Vocabularies (DDL, DQL, DML)
This logical interface is divided into three functional areas:
| Area | Purpose | Key Commands | Architectural Payoff |
| :--- | :--- | :--- | :--- |
| **DDL** (Definition) | Define structure. | `CREATE`, `ALTER`, `DROP` | Allocates physical files and catalog entries. |
| **DQL** (Query) | Read state. | `SELECT` | Optimizer decides between Index or Seq Scans. |
| **DML** (Manipulation) | Modify state. | `INSERT`, `UPDATE`, `DELETE` | Manages tuple versioning and MVCC headers. |
---
### 🧪 Observation Lab: Planner Freedom (SQL Equivalency)
Can a little rearranging on the waiter's pad change the work in the kitchen? Predict whether these two queries will produce different plans.
#### Compare Two Equivalent SQL Shapes
Compare the execution plans of two query variations that seek the same logical outcome: returning the names of all capybaras.
1. **Query A (Join first, then Filter)**:
```sql
EXPLAIN SELECT a.name, s.name
FROM animals a
JOIN species s ON a.species_id = s.id
WHERE s.name = 'Capybara';
```
2. **Query B (Subquery Filter first, then Join)**:
```sql
EXPLAIN SELECT a.name, sub.name
FROM animals a
JOIN (SELECT * FROM species WHERE name = 'Capybara') sub ON a.species_id = sub.id;
```
#### Two SQL Shapes Produce the Same Plan
In this fixture, both statements produce the **same plan**: filter `species` first, then join using a nested loop. Query B's subquery looks like a manual optimization, but Query A already gets that treatment.
#### The Planner Optimizes Intent, Not Nesting
Extra nesting is not an optimization by itself. The chef does not need a second pad to remember the same order. Next, let's see what makes this rearrangement legal.
---
## 1.2 - Relational Model (An academic detour)
<img src="assets/arch_relational_model_set.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
The two queries on the waiter's pad took the same path through the kitchen. Why was Postgres allowed to rearrange them?
Beneath SQL sits **Relational Algebra**. Model a relation as a **mathematical set of typed tuples**, and you get operations that compose predictably, plus rules for rearranging them without changing the answer. Most of the Query Planner's "magic" starts here.
---
### What a Relation Actually Is
A **Relation** is a set of tuples sharing a common attribute header. This sounds like a table, but two important constraints fall out of the mathematical definition that distinguish it from a spreadsheet:
**1. No guaranteed ordering.**
A set has no inherent sequence. Postgres does not promise that rows come back in the order they were inserted, or in any order at all, unless you explicitly ask for one with `ORDER BY`. This surprises a surprising number of engineers in production.
```sql
-- This returns rows in no guaranteed order.
SELECT * FROM animals;
-- This returns rows in a guaranteed order.
SELECT * FROM animals ORDER BY name;
```
**2. No duplicate rows (by default, unenforced).**
A true mathematical set prohibits duplicate elements. Postgres does not enforce this constraint automatically — you can insert two identical rows — but a `UNIQUE` or `PRIMARY KEY` constraint brings a table into formal compliance. The decision to not enforce uniqueness by default was deliberate: enforcing it has a cost, and not every table needs it.
---
### The Five Primitive Operations
Most common `SELECT` statements can be decomposed into five fundamental algebraic operations. These five are sufficient to express nearly any query over a relational schema:
| Symbol | Operation | What It Does | SQL Equivalent |
| :----- | :------------- | :--------------------------------------------------- | :--------------------------------------- |
| `σ` | **Selection** | Filter the set — keep only tuples matching predicate | `WHERE diet_type = 'Herbivore'` |
| `π` | **Projection** | Trim the header — keep only specified attributes | `SELECT name, diet_type` |
| `⋈` | **Join** | Combine two relations on a shared attribute | `JOIN species ON animals.species_id = species.id` |
| `∪` | **Union** | Merge two compatible sets, remove duplicates | `UNION` |
| `−` | **Difference** | Return tuples in the first set absent from the second | `EXCEPT` |
---
### The Operators Speak the Same Language
Each relational operation returns another relation. Filter the animals, join them to their species, keep only the names: the result of each step can feed the next. This property is called **closure**. No adapter required.
Closure lets the pieces fit together; **equivalence rules** tell the planner which rearrangements preserve the answer. In our previous lab, the Capybara filter mentions only `species`. Applying it before that inner join keeps the same matches while giving the join fewer rows to pair. That move is **predicate pushdown**.
The kitchen can change the choreography. It still owes you the meal you ordered.
---
## 1.3 - Summary: The Shape of Truth
### Chapter 1 Diagnosis Challenge: The Diet with Three Homes
The Cafe's first animal registry has grown into this table:
| `animal_id` | `animal_name` | `species_id` | `species_name` | `diet_type` |
| :--- | :--- | :--- | :--- | :--- |
| 1 | Babu | 10 | Capybara | Herbivore |
| 2 | Pip | 10 | Capybara | Herbivore |
| 3 | Nori | 20 | Red Panda | Omnivore |
The application team now wants to rename Capybara to *Hydrochoerus* and classify its diet as *Herbivore–Grazer*. One engineer proposes two `UPDATE` statements against the animal rows. Another proposes splitting species facts into their own relation.
Make the design call before reading the debrief:
1. Which facts in the table describe an individual animal, and which describe a species?
2. What contradiction can the two proposed updates create?
3. Sketch the smallest normalized design that gives each fact one authoritative home.
4. Which constraint keeps an animal from naming a species that does not exist?
5. What new cost does the normalized design introduce?
6. Would a PostgreSQL array automatically violate First Normal Form? Explain the real question instead of answering from the container type alone.
> [!IMPORTANT] Make the Design Call
> Write the two relations, their keys, and one sentence about the trade-off before continuing.
<div style="page-break-after: always;"></div>
### Design Debrief: One Species, One Story
`animal_id` and `animal_name` describe an individual animal. `species_name` and `diet_type` describe the species identified by `species_id`. Copying those species facts into every animal row gives one business fact many writable homes.
The two updates can succeed unevenly. The Cafe might rename every Capybara but change the diet for only Babu, leaving Pip attached to the old classification. Nothing in the denormalized table proves which copy is authoritative.
The smallest repair is:
```text
species(id PRIMARY KEY, name, diet_type)
animals(id PRIMARY KEY, name, species_id REFERENCES species(id))
```
The foreign key checks that each non-null `animals.species_id` matches an existing unique species key. The normalized design pays for a join when the application needs the combined profile, and it must account for the index and constraint work that preserve the relationship. That is a useful cost: the Cafe can reconstruct a species profile cheaply, but it no longer has to reconcile contradictory copies.
An array is not automatically a normalization failure. PostgreSQL can treat an array as one value in a declared domain. The practical questions are whether the attribute represents one value for this model, whether its internal members need independent identity or constraints, and whether the required dependencies can be enforced. A bag of tags and a list of separately governed business facts may look similar in brackets while deserving different schemas.
You started this chapter with tables, rows, and SQL.
You leave it with a different model: a database is a disciplined collection of facts, and SQL is the declarative boundary between what you want and how the engine gets it.
The important change is not that you learned normalization terminology. It is that you can now see why duplicated truth becomes operational risk. A bad schema is not merely untidy; it forces the engine and the application to negotiate contradictions.
You can also see why Postgres has room to be clever. Because your query describes a logical result, the planner can rearrange joins, push filters down, and choose a cheaper physical route without changing the answer.
> [!NOTE] State the Truth Once
> **Concept**: State the truth once. Let the engine find the path.
<div style="page-break-after: always;"></div>