Database Design & Modeling

Database design is the process of defining the structure of a database. A well-designed database is fast, maintainable, and correct. A poorly designed one leads to slow queries, data inconsistency, and maintenance nightmares.

This chapter covers the complete design process: from understanding requirements to creating the physical schema.

---

Learning Objectives

After completing this chapter, you will be able to:

• Describe the phases of database design.
• Gather and analyze requirements.
• Create conceptual, logical, and physical designs.
• Choose appropriate primary keys and data types.
• Follow naming conventions.
• Understand design tradeoffs.
• Answer design-related interview questions.

---

The Database Design Process

Database design follows a structured process with clear phases.

Requirement Analysis
    ↓
Conceptual Design (ER Model)
    ↓
Logical Design (Relational Model)
    ↓
Normalization
    ↓
Physical Design (Indexes, Partitions)
    ↓
Implementation (SQL DDL)
    ↓
Testing & Deployment
    ↓
Maintenance

---

Phase 1: Requirement Analysis

Goal: Understand what data needs to be stored and how it will be used.

Questions to Answer

• What are the main entities? (Customer, Order, Product)
• What information about each entity matters? (Name, price, date)
• How are entities related? (A customer places many orders)
• What business rules apply? (An order must have at least one item)
• How will the data be queried? (Find all orders by a customer)
• What are the performance requirements? (Sub-second response on 10M rows)

Deliverables

• List of entities and attributes.
• List of business rules.
• Sample queries and reports.
• Performance and security requirements.

---

Phase 2: Conceptual Design

Goal: Create a technology-independent model of the data.

What You Do

• Identify entities and attributes.
• Define relationships and cardinality.
• Identify primary keys.
• Note weak entities and constraints.

Tool

Entity-Relationship (ER) Diagram.

Example

An e-commerce conceptual model:

Customer (1) ── Places ── (N) Order (1) ── Contains ── (N) Product

Customer: Customer_ID, Name, Email, Phone
Order: Order_ID, Order_Date, Total
Product: Product_ID, Name, Price

---

Phase 3: Logical Design

Goal: Convert the ER model into relational tables (independent of any specific DBMS).

What You Do

• Map each entity to a table.
• Map attributes to columns.
• Define primary keys and foreign keys.
• Apply normalization rules.
• Define constraints (UNIQUE, NOT NULL, CHECK).

Example

CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE,
    Phone VARCHAR(20)
);

CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT NOT NULL,
    Order_Date DATE DEFAULT CURRENT_DATE,
    Total DECIMAL(10,2),
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
);

CREATE TABLE Order_Items (
    Order_ID INT,
    Product_ID INT,
    Quantity INT NOT NULL,
    Price DECIMAL(10,2),
    PRIMARY KEY (Order_ID, Product_ID),
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID)
);

CREATE TABLE Products (
    Product_ID INT PRIMARY KEY,
    Name VARCHAR(200) NOT NULL,
    Price DECIMAL(10,2) CHECK (Price > 0)
);

---

Phase 4: Normalization

Goal: Eliminate redundancy and prevent anomalies.

Process

• Apply 1NF (atomic columns).
• Apply 2NF (no partial dependency).
• Apply 3NF (no transitive dependency).
• Apply BCNF if needed.

Normalization is covered in detail in the Normalization section.

---

Phase 5: Physical Design

Goal: Decide how data will be stored and accessed.

Decisions

Decision	Options
Primary Key type	Auto-increment INT, UUID, natural key
Indexes	B+ Tree, hash, bitmap, composite, covering
Partitioning	Range, list, hash — by date or region
Storage engine	InnoDB (row), MyISAM (older), Columnar
File organization	Heap, sequential, hash, clustered
Compression	Page-level, column-level
Replication	Synchronous, asynchronous, read replicas

Example

-- Physical design decisions
CREATE TABLE Orders (
    Order_ID BIGINT AUTO_INCREMENT PRIMARY KEY,  -- BIGINT for scale
    Customer_ID INT NOT NULL,
    Order_Date DATE NOT NULL,
    Total DECIMAL(12,2)
) ENGINE=InnoDB
  PARTITION BY RANGE (YEAR(Order_Date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
  );

CREATE INDEX idx_orders_customer ON Orders(Customer_ID);
CREATE INDEX idx_orders_date ON Orders(Order_Date);

---

Phase 6: Implementation

Goal: Write the DDL and create the database.

CREATE DATABASE ECommerce;
USE ECommerce;

-- Create tables (as designed above)
-- Create indexes
-- Create views for reporting
-- Create users and set permissions
-- Load initial data

---

Primary Key Selection

Choosing the right primary key is critical.

Key Type	Example	Pros	Cons
Auto-increment INT	INT AUTO_INCREMENT	Simple, small, fast	Hard for replication, exposed sequential IDs
UUID	CHAR(36)	Globally unique, good for distributed systems	Larger (36 bytes), slower inserts
Natural Key	SSN, Email	Has business meaning	Changes over time, reuse issues
Composite Key	(Order_ID, Product_ID)	Naturally unique for junction tables	Larger FK references

Recommendation

Use BIGINT AUTO_INCREMENT for most tables. Use UUID only when data comes from distributed sources. Avoid natural keys as primary keys (use UNIQUE constraint instead).

---

Data Type Selection

Data	Recommended Type	Reason
IDs	INT or BIGINT	Fast, auto-increment
Names	VARCHAR(100-255)	Variable length, saves space
Email	VARCHAR(255)	Standard max length
Phone	VARCHAR(20)	Includes country codes, dashes
Dates	DATE (no time) or TIMESTAMP	Timezone-aware with TIMESTAMP
Money	DECIMAL(10,2) or BIGINT (paise)	DECIMAL avoids floating-point errors
Large text	TEXT or VARCHAR(10000)	TEXT for unlimited
Boolean	BOOLEAN or TINYINT(1)	MySQL: TINYINT; PostgreSQL: BOOLEAN

---

Naming Conventions

Consistent naming makes databases easier to understand.

Element	Convention	Example
Tables	Plural nouns	Customers, Orders, Products
Columns	Snake_case	customer_id, order_date
Primary Key	table_id	customer_id, order_id
Foreign Key	Same as referenced PK	customer_id
Indexes	idx_table_column	idx_orders_customer_id
Constraints	chk_table_rule	chk_products_price

---

Common Design Tradeoffs

Tradeoff	Normalized (3NF)	Denormalized
Storage	Less (no redundancy)	More (duplicate data)
Write speed	Slower (more tables, FKs)	Faster (single table)
Read speed	Slower (more joins)	Faster (no joins)
Data integrity	Higher (constraints)	Lower (potential anomalies)
Maintenance	Easier (one fact, one place)	Harder (update multiple copies)

When to Denormalize

• Reporting tables: Pre-joined for analytics.
• High-read, low-write systems: Cache joins in the table.
• Time-series data: Store pre-computed aggregates.

---

Interview Deep Dive

---

Key Takeaways

• Database design follows 6 phases: requirements → conceptual → logical → normalization → physical → implementation.
• Always start with requirement analysis — understand what your data looks like before designing.
• The ER model is technology-independent; the relational model is DBMS-independent.
• Choose primary keys carefully — prefer synthetic keys (BIGINT) over natural keys.
• Follow naming conventions for consistency.
• Design for normalized form first; denormalize only when measured performance requires it.
• The right data types and indexes make a significant performance difference.