Database Design Introduction

Why schema design matters#

A database schema is one of the most expensive things to change after the fact in a running application — unlike a bug in application code, which can usually be fixed and redeployed in minutes, a schema mistake often means a data migration across every existing row, coordinated with application code changes, sometimes with the application unable to fully stop serving traffic during the change. A missing foreign key or a wrongly-typed column discovered on day one costs a few minutes to fix; the same mistake discovered after a million rows have accumulated around it can cost days of migration work and real risk of data loss or downtime.

Good schema design is also what makes correct application behavior easy and incorrect behavior hard. A NOT NULL constraint means application code doesn't need to defensively check for missing data on every read. A foreign key means an order can never point at a user that doesn't exist, without a single line of application validation code. The database enforcing its own rules is far more reliable than trusting every code path, present and future, to remember to check.

The design process#

Designing a schema for a new feature or system generally follows three steps, in this order:

1. Identify the entities#

An entity is a distinct "thing" the application needs to store data about — usually a noun that shows up naturally when describing the domain out loud. For a simple e-commerce feature: users, products, orders. Each entity typically becomes one table.

2. Identify the relationships between entities#

Relationships describe how entities connect, and they come in three shapes:

• One-to-many — one user places many orders, but each order belongs to exactly one user. Modeled with a foreign key on the "many" side (orders.user_id referencing users.id).
• Many-to-many — an order can contain many products, and a product can appear on many orders. Modeled with a join table in between (order_items, holding order_id and product_id together, plus any attributes specific to that pairing like quantity).
• One-to-one — one user has exactly one profile record. Modeled either as a foreign key with a uniqueness constraint, or sometimes just as extra columns on the same table if the data always exists together.

3. Identify the constraints#

Constraints are the rules the database itself will enforce, rather than trusting application code to remember: which columns can never be empty (NOT NULL), which values must be unique (UNIQUE), which relationships must always point at something real (foreign keys), and which values must satisfy a business rule (CHECK, e.g. price >= 0).

A small worked example#

Walking through the process for "users place orders containing products":

Entities: users, products, orders.

Relationships: a user has many orders (one-to-many); an order can contain many products, and a product can appear on many orders (many-to-many, via a join table).

Resulting schema:

CREATE TABLE users (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Join table resolving the many-to-many between orders and products
CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Notice order_items carries its own attribute (quantity) that belongs to the pairing of an order and a product, not to either one alone — this is the tell that a many-to-many relationship needs its own table rather than just a foreign key on one side. Its composite primary key (order_id, product_id together) also enforces that the same product can't accidentally appear twice as separate rows on the same order.

Common mistakes#

• Skipping the relationships step and adding foreign keys reactively as bugs surface, rather than deciding up front how entities actually relate.
• Modeling a many-to-many relationship as a comma-separated list of ids in a text column instead of a proper join table — it looks simpler at first but makes basic queries (and referential integrity) far harder later, a point covered in more depth on the normalization page.
• Treating constraints as optional "nice to haves" added later — a NOT NULL or foreign key constraint added after bad data already exists requires cleaning up that data first, which is far more work than defining the constraint correctly from the start.