PostgreSQL Normalization: The Easy Way vs. The Correct Way (1NF, 2NF, 3NF)

When you’re starting a new project that involves data structures via PostgreSQL, you can handle it in two ways:

• The Easy Way (which eventually becomes hard to manage and inefficient)
• The Correct Way (which makes your life easier)

To do things the Correct Way, we use Normalization. In PostgreSQL, this means splitting one giant table into a few smaller, more manageable tables that link to each other.

The Easy Way (The Messy Spreadsheet example)

In the Easy Way, you create one table that has all of the data: e.g., the customer, the product, and the order details.

-- The "Everything" Table (Avoid this!)

CREATE TABLE big_messy_orders (
 order_id SERIAL PRIMARY KEY,
 customer_name TEXT,
 customer_email TEXT,
 product_names TEXT, -- e.g., "Laptop, Mouse, Keyboard" (A BIG NO-NO)
 total_price DECIMAL
);

Why this is bad:

• Update Nightmares: If a customer changes their email, you have to find and update every single order they ever made. Working with data has to be efficient; hunting through thousands of rows for one update is a recipe for data corruption.
• Search Struggles: Try asking Postgres to find every order that contains a “Mouse.” Because “Mouse” is buried inside a text list (Laptop, Keyboard, Mouse). The database has to scan every single character of every single row. It’s like reading an entire book just to find one word.

The Correct Way (Postgres Normalization)

What is Database Normalization?

Database Normalization is the process of organising a relational database into multiple tables to reduce data redundancy and ensure that every piece of information is stored in exactly one logical place.

First Normal Form (1NF): No Lists

Every cell must contain only one piece of information. No commas allowed.

-- Instead of a list of products, we give each item its own row

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT,
    product_name TEXT -- Just ONE product here
);

Second Normal Form (2NF): Remove Redundancy

Don’t store “Product Descriptions” or “Author Bios” in an order table. If the product name changes, you should only have to change it in one place.

-- We create a Products table

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price DECIMAL,
    description TEXT -- This description lives here ONLY
);

-- The Order table now points to the Product ID

CREATE TABLE order_items (
    order_id INT,
    product_id INT REFERENCES products(id), 
    quantity INT
);

Third Normal Form (3NF): No Middlemen

Data should only depend on the “ID” of that table. For example, a City depends on a Post Code, not the Customer_ID.

-- Move Post/City to its own lookup table

CREATE TABLE locations (
    post_code TEXT PRIMARY KEY,
    city TEXT
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    post_code TEXT REFERENCES locations(zip_code)
);

Performance: How Fast is Postgres?

Since we are separating data into multiple tables, you might think: “Does this slow things down?” Actually, it’s the opposite.

• Read Speed: Because normalized tables are “thinner” (fewer columns), Postgres can load them into memory much faster. When you use Indexes on your ID columns, finding a specific record among millions takes milliseconds.
• Write Speed: Postgres is designed for high-speed writing. Because it only has to update one specific row in one table (rather than thousands of rows in a messy table), your “Saves” and “Updates” stay fast as your app grows.

SQLite vs. PostgreSQL: The SLock Difference

SQLite: Best for Mobile Apps, Small Projects, and Local Storage

• The Lock: SQLite usually locks the entire database file when someone writes to it.
• The Result: If User A is saving a new profile, User B has to wait until User A is completely finished before they can save anything.
• Best for: Mobile apps, small blogs, or local tools such as to-do lists.

PostgreSQL: Enterprise-Grade Relational Database for Scalable Applications

• The Lock: Postgres uses Row-Level Locking (and MVCC).
• The Result: User A can update Row #1 while User B is simultaneously updating Row #500. They never see each other, and nobody has to wait.
• Best for: Any app where multiple people are clicking “Save” or “Buy” at the same time.

Running Postgres: Server Requirements

Unlike SQLite (which is just a file on your computer), Postgres runs as a “Service.”
Here is what you generally need to keep it happy:

• RAM: This is the most important. Postgres loves to “cache” (store) your data in memory for speed. For a small app, 1GB to 2GB of RAM is plenty.
• CPU: Most modern processors can handle Postgres easily. For heavy traffic, more cores mean more power.
• Storage: Use SSD storage if possible. Since databases are constantly reading and writing to the disk, an SSD makes a massive difference in speed compared to an old-fashioned hard drive.

Final Thoughts: Getting Your Database Design Right

Designing a PostgreSQL database might feel time consuming, but following the rules of Database Normalization (1NF, 2NF, and 3NF) is vital for a scalable application.

The “Easy Way” is tempting when you just want to get a project off the ground, but it will eventually slow you down with data redundncy. The “Correct Way” guarantees data integrity, fast query performance, and scaling. Take the extra time to plan your schema, break your data into logical pieces, and set up those Foreign Keys. Your database will run faster, your code will be cleaner, and your future self will thank you.