Conceptual vs Logical vs Physical Design

Discover the three essential phases of database design and build entity relationship diagrams that translate business requirements into working database schemas.

The Three Phases of Database Design

Database design happens in three distinct phases. Each phase serves a specific purpose and builds upon the previous one. Think of it like building a house — you start with a rough sketch, create detailed blueprints, then worry about electrical wiring and plumbing.

The three phases are conceptual design, logical design, and physical design. Each phase asks different questions and produces different outputs.

Conceptual Design: The Big Picture

Conceptual design captures what your business does. No technical details. No database jargon. Just pure business logic.

For BookVault, conceptual design answers questions like: What is a customer? What is an order? How do customers and orders relate to each other? You identify the main entities (things) and their relationships (connections).

Conceptual Design for BookVault

Notice what's missing from conceptual design: data types, table names, foreign keys. Those come later. Right now we focus on understanding the business.

Logical Design: Adding Structure

Logical design transforms your conceptual model into database language. You define entities, attributes, keys, and relationships using Entity-Relationship (ER) diagrams.

This phase is database-agnostic. You're not choosing MySQL versus PostgreSQL yet. You're creating a blueprint that works for any relational database.

BookVault Logical Design

The logical design shows that one customer can place many orders. Each order belongs to exactly one customer. The customer_id in the orders table creates this relationship.

Logical Design Deliverables

Physical Design: Making It Real

Physical design translates your logical model into actual database tables. Now you choose specific data types, create indexes, and write SQL CREATE TABLE statements.

This phase is database-specific. A VARCHAR(255) in MySQL might become TEXT in PostgreSQL. But the logical structure stays the same.

BookVault Physical Implementation

-- Physical design: BookVault customer table
-- Logical design becomes actual SQL
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Table created successfully
0 rows affected

-- Physical design: BookVault orders table
-- Foreign key implements the logical relationship
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Table created successfully
0 rows affected

Comparing the Three Phases

Each design phase serves a different audience and purpose. Understanding when to use each phase prevents confusion and saves time.

When Each Phase Matters Most

Different projects emphasize different phases. A quick prototype might skip conceptual design. An enterprise system needs extensive logical design. Understanding when to invest time in each phase separates good designers from great ones.

Conceptual Design is Critical When:

• Building systems for multiple stakeholders
• Requirements are unclear or changing
• The business domain is complex (healthcare, finance)
• You're working with non-technical decision makers

Logical Design Drives Everything When:

• Data integrity is crucial
• Multiple applications will use the same database
• You might migrate between database systems
• Complex reporting requirements exist

Physical Design Becomes Priority When:

• Performance is the main concern
• Working with huge data volumes
• Database-specific features are needed
• Legacy systems impose constraints

Real-World Design Evolution

BookVault's database will evolve as the business grows. New requirements emerge. Performance bottlenecks appear. Understanding how the three design phases adapt to change keeps your database healthy.

Conceptual changes are expensive — they affect the entire system. Adding a new entity like "Publisher" touches multiple tables and applications. Logical changes are moderate — adding attributes or changing relationships. Physical changes are cheapest — adding indexes or changing data types.

The next lesson dives deep into Entity-Relationship modeling — the heart of logical design. You'll learn the symbols, notation, and techniques that transform business requirements into precise database blueprints.

Quiz