# 0.0 Relations & Normalization
Postgres is fundamentally meticulous, and it achieves this through rigorous organization. It does not just store "data"; it manages a structured collection of facts about its world.
Before we dive into the internal mechanics of Postgres, we must understand why it is defined as a **Relational** Database. This logic relies on two core disciplines: the **Relationship** and **Normalization**.
## Facts and Relations
In the eyes of the meticulous database engine, every record in your database 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.
A collection of related facts that all share the same shape (or schema) is called a **Relation**. In common parlance, we call this a **Table**. A relation is not just a list; it is a mathematical set. Every "fact" (or **Tuple**) within that set must be unique and identifiable.
## 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 the string "Herbivore" ten thousand times is more than just a waste of space—it is a **Truth Hazard**.
If we decide to rename "Herbivore" to "Plant-Based," and the word is redundantly copied into every single animal's record, Postgres must perform a massive, exhausting search to find and update every copy. If it misses even one, the database has committed the ultimate sin: it now disagrees with itself. In technical terms, we have created an **Update Anomaly**.
Postgres avoids this through **Normalization**—the calm, methodical process of ensuring that every fact is stored in exactly **one place**.
Instead of copying "Herbivore" into every animal record, the wise database engine stores it once in a central **`species`** ledger. Every animal record in the **`animals`** ledger then carries a tiny, 4-byte pointer—a **Foreign Key**—linking it back to that single source of truth.
### 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, Postgres uses the **`species_id`** (1) to perform a **Join** against the species table, reconstructing his profile in memory. The fact that Babu is a Capybara is not stored twice; it is **linked** once. This is the heart of the **Relational** model: it minimizes I/O overhead and ensures that there is only ever one version of the truth.
> [!TIP]
> The mathematical machinery behind this model — why a table is a **set of typed tuples**, why SQL can be rearranged freely by the planner — is the subject of **[[Chapter 0/0.2 - The Relational Model|0.2 The Relational Model]]**. It is the academic foundation of our Cafe.
---
### 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 rigid 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
direction LR
species ||--o{ animals : ""
suppliers ||--o{ supply_deliveries : ""
ingredients ||--o{ supply_deliveries : ""
ingredients ||--|| flavors : ""
animals ||--o{ animal_favorites : ""
ingredients ||--o{ animal_favorites : ""
dishes ||--o{ dish_ingredients : ""
ingredients ||--o{ dish_ingredients : ""
animals ||--o{ orders : ""
orders ||--o{ order_items : ""
dishes ||--o{ order_items : ""
```
---
## The Normalized Tiers of the Cafe
We can divide our Cafe's data model into three distinct logical areas, each serving a specific architectural purpose.
### 1. The Core Entities (`species` & `animals`)
This is a standard **normalized hierarchy**. Species-level attributes are stored once, and individual patron records point back to them. Changing a species name here propagates to every animal instantly, without requiring a single row migration in the `animals` table.
### 2. The High-Precision Pantry (`ingredients` & `flavors`)
Here, we store the "Scent Science." Ingredients are linked to high-dimensional **Flavor Vectors** and scent notes. This is where we will exercise **[[Chapter 2/2.4 - The Scent Tracker (Vector Search)|Vector Indexes]]** and complex metadata linking.
### 3. The Transactional Hustle (`orders`, `dishes`, & `supply_deliveries`)
These tables capture the movement of data—the **Facts of Action**.
- **Many-to-Many Links**: Tables like `dish_ingredients` and `order_items` use pairs of foreign keys to link two disparate facts together (e.g., "This Order includes this Dish").
- **The Infinite Ledger**: `supply_deliveries` records every shipment. This table will grow to billions of rows, making it our primary candidate for **[[Chapter 7/7.3 - Table Partitioning|Table Partitioning]]**.
---
In the next section, we will look at **[[Chapter 0/0.1 - The Language of the Cafe (SQL)|SQL]]** — the dialect Postgres speaks, and why it is deliberately vague about physical details.
---
| ← Previous | ↑ Table of Contents | Next → |
| :--- | :---: | ---: |
| | [[Learn You a Postgres for Great Good\|Home]] | [[Chapter 0/0.1 - The Language of the Cafe (SQL)\|0.1 The Language of the Cafe (SQL)]] |