PostgreSQL Normalization: How to Design Clean Databases

Splitting Data into Related Tables

A single table that stores everything—customers, orders, and products—might look convenient, but it causes problems. Alice Shen's name and email appear four times. If she changes her name, every row must be updated. That's a data integrity issue, especially as the table grows to thousands of rows. Also, one order with two products forces the order ID to repeat, turning the table into a flat list of line items that doesn't represent a real order.

The fix is to split the data into four tables: customers, products, orders, and order_items. Alice's information lives only in customers. Other tables reference her by her primary key (the ID), so if her name changes, nothing else needs updating—they point to the updated version automatically.

Setting Up the Environment

Start PostgreSQL by clicking Start PostgreSQL. The environment includes VS Code and a terminal. Open the integrated terminal in VS Code by clicking the three lines menu, then Terminal > New Terminal. Maximize the terminal for more space.

Verify the PostgreSQL service is running:

sudo systemctl status postgresql

You should see it's active and running. Connect to the movie_store database:

sudo -u postgres psql movie_store

Reviewing the Pre-Populated Tables

Four tables are already created: products, customers, orders, and order_items.

A primary key is a unique identifier for each row, set by PostgreSQL, never repeated. It acts as an address that other tables use to reference that row without storing any information about it (like name or email).

A foreign key is a pointer back to the referenced table. For example, the customer_id column in orders references the id primary key in customers. This enforces a constraint: you cannot insert a customer ID that doesn't exist.

Test this by trying to insert an invalid customer ID:

INSERT INTO orders (customer_id) VALUES (1999);

You'll get an error: insert or update on table "orders" violates foreign key constraint.

Understanding Many-to-Many Relationships with a Junction Table

One order can contain multiple products (e.g., a Baby Yoda toy, a lightsaber, and a Funko Pop). At the same time, one product can be bought by many people. This is a many-to-many relationship. Using foreign keys on both sides alone would still cause repeating rows.

The solution is a junction table, here called order_items. Each row in order_items records which order contains which product and in what quantity. The orders table references the customer, and order_items connects orders to products.

View the schema of order_items:

\d order_items

This shows two foreign keys: order_id referencing orders, and product_id referencing products.

Joining Tables to See the Full Picture

You can reconstruct the original messy table by joining all four tables:

SELECT * FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id;

The result is the same flat list you started with, but now the data is stored cleanly in separate tables.

What Is Normalization?

Normalization is a set of rules to avoid the mess of duplicated and mixed data:

1. Don't repeat the same fact in multiple rows. Alice's email should appear only once.
2. Don't store data you can look up elsewhere. Each table should contain only data that belongs to it.
3. Each table should describe exactly one thing. Products only about products, orders only about orders, customers only about customers, and order_items only about the relationship between orders and products.

One fact, one place, no duplications.