Skip to content

Database Design

Plan a database from requirements to schema: entities, attributes, and relationships.

What you'll learn

  • The database design process: requirements to schema
  • Identifying entities and attributes from requirements
  • Choosing primary keys and data types wisely
  • Designing for query patterns, not just data shape
  • Common design patterns and anti-patterns

Concept

The Design Process

Good database design follows a process: (1) understand the requirements and what questions the database must answer, (2) identify the entities (nouns: users, orders, products), (3) identify relationships between entities, (4) choose attributes and data types for each entity, (5) normalize to reduce redundancy, (6) add indexes based on expected queries, and (7) review and iterate.

Identifying Entities and Attributes

Read the requirements and highlight the nouns — these are your entities. A "users place orders for products" requirement gives you three entities: users, orders, products. Each entity becomes a table. Attributes are the properties: a user has a name, email, and created_at. Choose data types carefully — use DECIMAL for money, TIMESTAMP for dates, VARCHAR with sensible lengths for strings.

Choosing Primary Keys

Every table needs a primary key. The best choice is a surrogate key — an auto-incrementing integer (SERIAL/BIGSERIAL) or a UUID. Avoid using natural keys (like email or username) as primary keys — they can change, and changing a primary key cascades to every foreign key that references it. Surrogate keys are stable, compact, and fast to index.

Designing for Query Patterns

Do not just model the data — model the queries. What will the application ask most often? "Show a user's recent orders" means you need an index on (user_id, created_at). "Search products by name" means you need a full-text index or a LIKE with an index. Design indexes after you know the queries, not before. Consider read-heavy vs write-heavy patterns and denormalize selectively for hot paths.

Anti-Patterns to Avoid

  • EAV (Entity-Attribute-Value) — storing attributes as rows in a key-value table. It seems flexible but makes queries impossibly complex.
  • God table — one massive table with 100+ columns. Split into related tables.
  • Polymorphic associations — a foreign key that points to different tables depending on a type column. Use separate foreign keys or a proper association table instead.
  • Storing arrays as CSV — always use a junction table or a native array type.

Example

              -- A well-designed e-commerce schema (simplified)
CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  name       VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
  id         BIGSERIAL PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  price      DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
  category   VARCHAR(100) NOT NULL,
  in_stock   BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  status     VARCHAR(20) NOT NULL DEFAULT 'pending',
  total      DECIMAL(10, 2) NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- Indexes based on expected queries
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
CREATE INDEX idx_products_category ON products (category);
            

A clean e-commerce schema with surrogate keys, proper foreign keys, a junction table for order items, and indexes designed for common query patterns.

Try it

Common mistakes

The mistake

Designing the schema before understanding the queries

The fix

Talk to stakeholders about what questions the database must answer. Design tables to hold the data, then design indexes to serve the queries efficiently.

The mistake

Using natural keys (email, username) as primary keys

The fix

Natural keys can change, and changing a PK cascades to every FK. Use surrogate keys (SERIAL or UUID) that never change and are fast to index.

Practice Database Design

1 exercise

Practice Database Design

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors