Chapter 9: The Flow of System Design — Conceptual, Logical, and Physical Stages

What you'll learn in this chapter

Explain where database design fits within the overall flow of system development
Distinguish the three stages — conceptual, logical, and physical design — and state the deliverable of each
Understand that the design process is iterative

Prerequisites

You should understand through Chapter 8: Master vs. Transaction Data. This chapter begins Part 2, "Design."

The Overall Flow of System Development

Database design is one part of the overall system-development process. Let's get a sense of where it fits within the big picture.

Database design belongs to the design phase. It's the process of translating "what to build," gathered during requirements definition, into a concrete "data structure."

Don't skip design and jump straight into implementation

A trap junior engineers often fall into is "just creating tables and starting to write code." That's fine for a prototype, but if you start writing CREATE TABLE for a production system's design right from the start, missed requirements, gaps in relationships, and broken normalization surface later — piling up the "hard to fix later" problems covered in Chapter 3.

Database Design Proceeds in Three Stages

The standard way to carry out database design is to move from abstract to concrete across three stages.

In a nutshell, here's what each stage does:

Stage	What you do	What comes out of it
Conceptual design	Identify the business's cast of characters (entities) and their relationships	A conceptual ER diagram (an abstraction level that works regardless of DBMS)
Logical design	Translate entities into RDB tables and normalize them	A logical ER diagram, table list, and column list
Physical design	Turn it into concrete table definitions that run on an actual DBMS	`CREATE TABLE` statements, index definitions

Conceptual Design — Modeling the Real World

Conceptual design is the stage where you analyze the business to identify "what things show up as data" and "what relationships they have." Implementation topics like RDB vs. NoSQL don't come up yet.

Translating Business Activity Into Entities

An Example Conceptual ER Diagram

Terminology

Entity: a "thing" that appears in the business (employee, customer, product, order, etc.). A candidate that will eventually become a table.

Cardinality: the multiplicity of a relationship — one-to-one, one-to-many, or many-to-many. Covered in detail in Chapter 12: E-R Diagrams.

Logical Design — Detailing Based on Relational Theory

Logical design is the stage where you organize the conceptual model into "RDB tables." Normalization (see Chapter 11 for details) and setting primary and foreign keys (Chapter 10) are the core work of this stage.

An Example Logical-Design Deliverable

At the logical-design stage, it's fine for data types to still be at an abstract level — "string," "number," "date."

Customer (CustomerID, CustomerName, PhoneNumber, Email, Address)
Product (ProductID, ProductName, CategoryID, StandardPrice)
Order (OrderID, CustomerID, OrderDateTime, ShippingAddress, TotalAmount)
OrderLineItem (OrderID, ProductID, Quantity, UnitPrice, Subtotal)

Notation like this — "TableName (attribute1, attribute2, ...)" — is also called a relation. Primary keys are sometimes shown underlined (like CustomerID).

Physical Design — Making It Runnable on an Actual DBMS

Physical design is the stage where you flesh out the logical model to a level that actually runs on a real DBMS. The following decisions happen here.

An Example Physical-Design Deliverable

An example physical-design deliverable (PostgreSQL)
CREATE TABLE customers (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    phone         VARCHAR(20),
    email         VARCHAR(255) UNIQUE NOT NULL,
    address       TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_customers_email      ON customers(email);
CREATE INDEX idx_customers_created_at ON customers(created_at);

Data types (VARCHAR(100), NUMERIC(12, 2), TIMESTAMPTZ, etc.), indexes, and partitions — the "implementation details" — are only made concrete here for the first time.

The Benefits of Splitting Into Three Stages

Why split it into three stages? Writing all the way to CREATE TABLE in one go looks faster, but separating the stages has clear benefits.

Benefit	Explanation
You can insert a "translation" step between the business and the code	Business stakeholders discuss the conceptual model, and engineers translate it into logical, then physical
A different person can own each level of abstraction	For example, a business analyst handles the conceptual level, a DBA handles the physical level
Partial changes are easier	Switching the DBMS from MySQL to PostgreSQL can mostly reuse the conceptual and logical models as-is
You can catch oversights stage by stage	You might notice a relationship at the logical stage that wasn't apparent at the conceptual stage, and so on

The Design Process Is Iterative

Real-world design doesn't move one-way from conceptual to logical to physical — it's an iterative process where you go back once you find a problem.

For example, if you notice mid-implementation, "wait, isn't this actually many-to-many in the business, not one-to-many?", you go back to conceptual design and revisit the relationship. This isn't a failure — it's a natural part of the design process.

Practical advice for junior engineers

Always talk with the business stakeholders during the conceptual-design stage. Working only from documents leaves gaps in understanding
Be conscious of normalization during the logical-design stage (Chapter 11). "I'll fix it later" tends not to happen
Be conscious of real data volumes and access patterns during physical design. A table that will only ever hold 1,000 rows doesn't need partitioning; a table that gets 1,000 writes a second should account for it from the start

Exercises

Exercise 1: What to think about at each stage

Suppose you've been put in charge of designing the database for an internal SNS. Which stage — conceptual, logical, or physical design — does each of the following tasks belong to?

Organizing the relationships between "posts," "comments," and "users" in an ER diagram
Making the posts table's body column a TEXT type
Organizing the posts table up through third normal form and setting foreign keys
Creating an index on created_at to speed up post searches
Interviewing a business stakeholder and confirming that "a post can sometimes have multiple images attached"
Deciding whether to use PostgreSQL or MySQL

Sample answer

#	Stage	Reason
1. Organize relationships in an ER diagram	Conceptual design	Organizing entities and relationships. Data types and the like aren't a concern yet
2. Decide on the TEXT type	Physical design	Deciding on a concrete DBMS data type
3. Third normal form + foreign keys	Logical design	Normalization and reference relationships at the level of tables (relations)
4. Create an index	Physical design	Physical optimization for performance
5. Business interview	Conceptual design	The work of discovering entities and relationships
6. Choose a DBMS	Physical design	Deciding on the implementation platform

A common mistake: assuming 1 and 5 are "logical design." Logical design is the stage where you "organize entities you've already found as tables" — discovering new entities is the responsibility of conceptual design.

Exercise 2: A judgment call in iterative design

While designing an SNS like Twitter, you notice the following problem at the logical-design stage.

You were planning to hold the "follow" relationship as a column on users — following_user_ids, an array of the IDs being followed — but this makes "unfollowing" logic complicated (removing an entry from the array), and counting the number of people followed would require something like array_length(following_user_ids).

Which stage should you go back to, and what should you revisit?

Sample answer

Stage to go back to: logical design (possibly conceptual design too).

What to revisit: express the "follow" relationship not as an array column on the users table, but as an independent junction table, follows (follower_id, following_id). This is a "many-to-many relationship" (a user can follow multiple users, and be followed by multiple users), and representing it with a junction table is the standard approach (covered in detail in Chapter 14: Many-to-Many).

The corrected design
CREATE TABLE follows (
    follower_id   INTEGER NOT NULL REFERENCES users(id),
    following_id  INTEGER NOT NULL REFERENCES users(id),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id <> following_id)  -- can't follow yourself
);

Why this works:

An array column is an "unnormalized" design (it fails to satisfy first normal form), which is inefficient for searching, updating, and aggregating alike
With a junction table, WHERE following_id = ? can quickly search "who is following this person"
Unfollowing is just a single-row delete: DELETE FROM follows WHERE follower_id = ? AND following_id = ?

A common mistake: deciding "there's not much data, so an array is enough." A follow feature will inevitably see rising performance demands in the future, and an array design breaks down at just a few thousand followers. It's safer to design for many-to-many from the start.

Alternative approach: using a graph database (like Neo4j) is also an option, but unless you're operating at Twitter's scale, an RDB plus a junction table is plenty.

Summary

Here's a recap of what this chapter covered.

Database design belongs to the design phase of overall system development
It proceeds abstract to concrete across three stages: "conceptual design → logical design → physical design"
Each stage's deliverable: a conceptual ER diagram → a logical model (normalized tables) → CREATE TABLE statements
Design is iterative — when you notice a problem, you go back to the appropriate stage and revisit it

The next chapter looks in detail at the concept of "keys" — the core of design (primary keys, foreign keys, candidate keys, composite keys).

Next chapter

Chapter 10: Database Key Concepts

What you'll learn in this chapter​

The Overall Flow of System Development​

Database Design Proceeds in Three Stages​

Conceptual Design — Modeling the Real World​

Translating Business Activity Into Entities​

An Example Conceptual ER Diagram​

Logical Design — Detailing Based on Relational Theory​

An Example Logical-Design Deliverable​

Physical Design — Making It Runnable on an Actual DBMS​

An Example Physical-Design Deliverable​

The Benefits of Splitting Into Three Stages​

The Design Process Is Iterative​

Exercises​

Summary​