Learn SQL with PostgreSQL - Relational Design Concepts, Normalization & DDL

#Introduction

Welcome to episode 2 of the Learn SQL with PostgreSQL series! In episode 1, we understood the history of the relational model and the reasons for choosing PostgreSQL. Now it's time to jump into the most important technical foundation: how to design a good database. Most real-world database problems — duplicate data, inconsistency, slowing queries — actually stem from poor design, not the wrong engine.

In this episode, we'll cover relational data modeling with Entity Relationship Diagrams (ERD), the role of Primary Keys, Foreign Keys, and Unique Constraints, the data normalization principles from 1NF to BCNF, and then hands-on practice with Data Definition Language (DDL).

#Relational Data Modeling

Before writing CREATE TABLE, a good database designer first draws the big picture. Data modeling is the process of turning real-world business needs into an executable database structure.

#Entity Relationship Diagram (ERD)

An Entity Relationship Diagram (ERD) is a diagram that describes entities, attributes, and the relationships between entities. In the context of a relational database:

• Entity is an object whose data we want to store: users, products, orders.
• Attribute is a column on an entity: email, price, status.
• Relationship is the connection between entities: one user can have many orders.

users 1 ────── n orders n ────── 1 products
(id)           (id, user_id)      (id)
(email)        (product_id)       (name)
               (quantity)         (price)

How to read the diagram above: one users has many orders, and each orders references one products. This kind of relationship is known as one-to-many, and it's the most common pattern in relational database design. There are also one-to-one and many-to-many relationships, which are usually broken up with a junction table.

#Primary Key, Foreign Key, and Unique Constraint

These three concepts are the backbone of relational database integrity:

• Primary Key (PK): the column (or combination of columns) that uniquely identifies every row. There can be no duplicates and no NULLs.
• Foreign Key (FK): a column on a table that references the Primary Key of another table. The FK is what "binds" the relationship between tables and maintains consistency.
• Unique Constraint: guarantees there are no duplicate values in a specific column, without having to be a primary key.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);
 
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total NUMERIC(10,2) NOT NULL
);

Notice: the user_id column in the orders table is a foreign key referencing id in the users table. Thanks to this FK, the database will reject an order whose user_id doesn't exist in the users table — integrity is maintained automatically by the engine.

#Data Normalization Principles

Normalization is the process of designing table structures to minimize data duplication and prevent anomalies. Repeated data isn't just a waste of storage — it's a time bomb of inconsistency: if one copy is updated but the others aren't, which one is correct?

#Normal Form 1, 2, 3, and BCNF

Normalization works through levels called Normal Forms (NF). The higher the level, the cleaner the structure:

Let's look at a practical example. The following table violates 1NF because the tags column stores multiple values in one column:

CREATE TABLE products_bad (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT NOT NULL
);

The 1NF fix is to move tags into a separate table that stores one tag per row. And so on: each normalization level eliminates one class of data anomaly.

#Normalization vs Denormalization

It's important to note: normalization isn't a dogma without exceptions. In the real world, denormalized tables (deliberately adding duplication) are often used for analytical performance — for example storing an aggregated total_revenue column, or using JSONB for flexible attributes. We'll look at when denormalization is justified in episode 20 (the e-commerce case study).

#Data Definition Language (DDL) in PostgreSQL

Once the design and normalization are solid, it's time to express them in SQL. Data Definition Language (DDL) is the set of commands for defining and managing database structure — databases, tables, columns, and constraints.

#Creating & Managing Databases

CREATE DATABASE shop;
DROP DATABASE shop;

Important note: DROP DATABASE can't be run while you're still connected to the database in question. Switch to another one first, e.g. the postgres database, before dropping shop.

#Creating Tables

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    full_name TEXT NOT NULL,
    age INTEGER CHECK (age >= 17),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Notice the elements: SERIAL PRIMARY KEY for an auto-increment id, NOT NULL for required columns, UNIQUE for email, CHECK for value validation, and DEFAULT now() for automatic timestamps. We'll break down these data types and constraints more deeply in episode 3.

#Altering, Dropping, and Truncating Tables

Table structure rarely stays completely unchanged after the initial creation. ALTER TABLE is the command for structural evolution:

ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO members;

Then there are two commands whose meanings are often mixed up:

• TRUNCATE TABLE: quickly removes ALL rows, but the table and its structure remain. Can't be used with WHERE.
• DROP TABLE: permanently removes the table along with its structure and all its contents.

TRUNCATE TABLE orders;
DROP TABLE orders;

#Working in psql

All the DDL above can be executed directly in psql. To verify the results, use the meta-commands we learned in episode 0:

\dt
\d users

\dt lists the tables, while \d users shows the columns, types, and constraints of the users table.

#Common Mistakes When Creating Tables

#Closing

In this episode 2 we've built a solid design foundation: modeling data with Entity Relationship Diagrams, understanding the roles of Primary Keys, Foreign Keys, and Unique Constraints, mastering the normalization principles from 1NF to BCNF, and practicing DDL to create, alter, and drop databases and tables.

Key takeaways:

• An ERD is the roadmap of database design — draw it first before writing queries.
• Foreign Keys maintain relationship integrity between tables automatically.
• Normalizing up to 3NF minimizes data duplication and anomalies.
• Denormalization is allowed, but must be conscious and documented.
• CREATE/ALTER/DROP/TRUNCATE is the DDL toolkit you must master.

In episode 3, we'll dive deeper into the raw materials of a database: Data Types in PostgreSQL and constraint management — from numeric, string, date-time, boolean, and UUID types, to various constraints like NOT NULL, FOREIGN KEY with referential actions, and CHECK. Your skill set will shift from "can create a table" to "creating correct tables".