📐 Database Design Principles

Why Design Matters

Database design is to software what architecture is to a building. Bad architectural choices produce cramped rooms, cracked foundations, and renovations that cost more than the original build. Bad database design produces slow queries, data that contradicts itself, and features that become terrifying to change.

A well-designed schema pays you back every day. It:

• Keeps data consistent — one fact lives in exactly one place
• Prevents anomalies when you insert, update, or delete
• Makes common queries fast and simple to write
• Adapts gracefully as requirements grow

We'll focus on relational design, where the discipline is most developed, and note where NoSQL flips the rules.

The Design Process

Good design moves from fuzzy requirements to a concrete schema in orderly steps. You rarely do all this ceremony for a tiny app, but knowing the stages keeps you honest.

1. Requirements — what data must we store, and how will it be queried?
2. Conceptual design — sketch the entities and their relationships as an ER model.
3. Logical design — turn entities into tables with primary and foreign keys.
4. Normalization — reorganize to remove redundancy.
5. Physical design — choose concrete types, add indexes for the queries you'll run.

Entity-Relationship Modeling

An entity-relationship (ER) diagram is a picture of your data's structure. It has three ingredients:

• Entities — the "things" you track: Customer, Order, Product. Each becomes a table.
• Attributes — properties of an entity: a Customer's name and email. Each becomes a column.
• Relationships — how entities connect: a Customer places Orders.

Here's an ER diagram for a small e-commerce store. Read ||--o{ as "one to many":

Every relationship is enforced by a foreign key: ORDER.customer_id points at CUSTOMER.id, PRODUCT.category_id at CATEGORY.id, and so on. The diagram is the blueprint; the foreign keys are the steel that holds it together.

Relationship Types

Cardinality describes how many instances of one entity relate to another. There are three shapes, and recognizing them tells you exactly how to lay out your tables.

Resolving Many-to-Many

A relational table can't hold "many" foreign keys in one column. So a many-to-many relationship is always broken into two one-to-many relationships using a junction table (also called a join or bridge table) in the middle.

Students take many courses; each course has many students. The enrollments table sits between them:

CREATE TABLE students (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id    SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

-- The junction table: one row per (student, course) pairing
CREATE TABLE enrollments (
    student_id  INTEGER REFERENCES students(id),
    course_id   INTEGER REFERENCES courses(id),
    enrolled_on DATE,
    grade       VARCHAR(2),
    PRIMARY KEY (student_id, course_id)   -- composite key prevents duplicates
);

The composite primary key (student_id, course_id) makes each pairing unique — a student can't enroll in the same course twice — and the junction table has a natural home for facts about the pairing itself, like the grade and enrollment date.

Normalization: 1NF, 2NF, 3NF

Normalization is the systematic process of organizing columns and tables to minimize redundancy. Each "normal form" is a rule; you apply them in order, and each one removes a specific kind of duplication that causes update problems.

First Normal Form (1NF)

Rule: every cell holds a single, atomic value — no lists crammed into one column — and each row is unique.

Not in 1NF (a comma-separated list in one cell):

In 1NF (one value per cell, one course per row):

Second Normal Form (2NF)

Rule: be in 1NF, and every non-key column must depend on the whole primary key — not just part of it. This only bites when you have a composite primary key.

Not in 2NF: the key is (student_id, course_id), but student_name depends only on student_id:

Problem: "John Smith" is repeated on every enrollment. Fix: split the student's own facts into a students table, leaving only pairing facts (the grade) in enrollments.

Third Normal Form (3NF)

Rule: be in 2NF, and no non-key column may depend on another non-key column (a "transitive" dependency).

Not in 3NF: category_name depends on category_id, which is not the key:

Problem: rename the category and you must edit every product in it. Fix: move category names into their own table; products keep just category_id.

Worked example: normalizing an orders table to 3NF

Start with one bloated table jamming customer, product, and order data together:

-- ❌ Everything in one table: customer & product data repeat on every line
CREATE TABLE customer_orders (
    order_id      INT,
    order_date    DATE,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    product_id    INT,
    product_name  VARCHAR(100),
    product_price DECIMAL(10,2),
    quantity      INT
);

After decomposing to 3NF, each fact lives exactly once across four focused tables:

CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    order_date  DATE,
    customer_id INT REFERENCES customers(id)
);

CREATE TABLE order_items (
    order_id   INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity   INT,
    unit_price DECIMAL(10,2),         -- price AT TIME OF SALE, kept on purpose
    PRIMARY KEY (order_id, product_id)
);

Now a customer's email is stored once no matter how many orders they place, and a product's details once no matter how many orders include it. Note unit_price is deliberately copied into order_items: the price the customer paid is a fact about that order and must not change when the product's current price does.

When to Denormalize

Normalization is the default, but it isn't a religion. Highly normalized data means more JOINs, and on read-heavy workloads those joins can become a bottleneck. Denormalization deliberately re-introduces some redundancy to make reads faster.

Reasonable reasons to denormalize

• Reporting and analytics dashboards that read constantly but rarely write
• A query joins many tables and has become measurably slow
• The data changes rarely, so keeping copies in sync is cheap

Common techniques

• Pre-computed values — store an order_total instead of summing items every read
• Summary tables — a nightly-built table of aggregated stats
• Materialized views — cache a complex query's result on disk

Practice & Quiz

CREATE TABLE users (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
    id       SERIAL PRIMARY KEY,
    user_id  INT REFERENCES users(id),   -- author
    title    VARCHAR(200) NOT NULL,
    body     TEXT
);

CREATE TABLE comments (
    id       SERIAL PRIMARY KEY,
    post_id  INT REFERENCES posts(id),   -- which post
    user_id  INT REFERENCES users(id),   -- who commented
    body     TEXT
);

CREATE TABLE employees (
    emp_id        INT PRIMARY KEY,
    emp_name      VARCHAR(100),
    dept_id       INT,
    dept_name     VARCHAR(100)   -- hmm...
);

CREATE TABLE departments (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id   INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id  INT REFERENCES departments(id)   -- name lives once, over there
);

Best Practices & Pitfalls

✅ Do

• Start from the questions your app will ask the data
• Normalize to 3NF by default — it's the right choice for most apps
• Give every table a primary key and enforce relationships with foreign keys
• Use a junction table for every many-to-many relationship
• Adopt consistent naming (e.g. singular or plural table names, snake_case) and stick to it

❌ Don't

• Store lists or CSV strings in a single column — that's an unnormalized trap
• Copy the same fact into many rows and hope to keep them in sync
• Denormalize before you've measured an actual performance problem
• Forget that a paid price on an order is a real, separate fact from the product's current price

Summary

📚 Additional Resources

• PostgreSQL — Data Definition (tables, constraints, keys)
• MongoDB — Data Modeling Introduction
• MDN — Database normalization (glossary)

🚀 What's Next?

Theory becomes practice next: Setting up PostgreSQL. You'll install a real database, create the tables you designed here, and get ready to connect it to a Node.js application.