Database Design Case Studies

Theory is essential, but the real test of database design skills is applying them to real-world problems.

This chapter presents six case studies. Each one walks through the design process: entities, relationships, ER diagram, relational schema, and key design decisions.

---

Learning Objectives

After completing this chapter, you will be able to:

• Design a database schema for any given problem statement.
• Identify entities, relationships, and constraints from requirements.
• Choose appropriate keys, data types, and normalization levels.
• Handle edge cases and design tradeoffs.
• Answer system design interview questions involving databases.

---

Case Study 1: Library Management System

Requirements

• The library has many books. Each book has an ISBN, title, author, publisher, and year.
• Each book can have multiple copies (e.g., 3 copies of “DBMS Fundamentals”).
• Members can borrow books. A member has an ID, name, email, and phone.
• A member can borrow up to 5 books at a time.
• Each borrowing record tracks the checkout date, due date, and return date.
• Late returns incur a fine.

Entities

• Book
• BookCopy
• Member
• Borrowing

ER Diagram (Conceptual)

Book (1) ── Has ── (N) BookCopy
Member (1) ── Borrows ── (N) Borrowing
BookCopy (1) ── Borrowed_In ── (N) Borrowing

Relational Schema

CREATE TABLE Books (
    ISBN VARCHAR(13) PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    Author VARCHAR(100),
    Publisher VARCHAR(100),
    Year INT
);

CREATE TABLE BookCopies (
    Copy_ID INT PRIMARY KEY,
    ISBN VARCHAR(13) NOT NULL,
    Location VARCHAR(50),
    Status VARCHAR(20) CHECK (Status IN ('Available', 'Borrowed', 'Damaged')),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);

CREATE TABLE Members (
    Member_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE,
    Phone VARCHAR(20),
    Join_Date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Borrowings (
    Borrowing_ID INT PRIMARY KEY,
    Member_ID INT NOT NULL,
    Copy_ID INT NOT NULL,
    Checkout_Date DATE NOT NULL,
    Due_Date DATE NOT NULL,
    Return_Date DATE,
    Fine DECIMAL(10,2) DEFAULT 0,
    FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID),
    FOREIGN KEY (Copy_ID) REFERENCES BookCopies(Copy_ID)
);

Key Design Decisions

• Separate Book and BookCopy: A book is a title; each physical copy is tracked individually. This allows knowing which specific copy was borrowed.
• Nullable Return_Date: NULL means the book is still borrowed.
• Fine column: Calculated and stored when the book is returned.

---

Case Study 2: E-Commerce Platform

Requirements

• Customers can browse products and place orders.
• Each product belongs to a category.
• An order contains multiple items (products and quantities).
• Customers have a shipping address and payment information.
• Orders have statuses: Pending, Shipped, Delivered, Cancelled.

Entities

• Customer
• Category
• Product
• Order
• OrderItem

Relational Schema

CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE,
    Phone VARCHAR(20),
    Address TEXT,
    Registered_Date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Categories (
    Category_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Parent_Category_ID INT,
    FOREIGN KEY (Parent_Category_ID) REFERENCES Categories(Category_ID)
);

CREATE TABLE Products (
    Product_ID INT PRIMARY KEY,
    Category_ID INT,
    Name VARCHAR(200) NOT NULL,
    Description TEXT,
    Price DECIMAL(10,2) CHECK (Price > 0),
    Stock_Quantity INT DEFAULT 0,
    FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID)
);

CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT NOT NULL,
    Order_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Status VARCHAR(20) CHECK (Status IN ('Pending', 'Shipped', 'Delivered', 'Cancelled')),
    Total DECIMAL(12,2),
    Shipping_Address TEXT,
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
);

CREATE TABLE Order_Items (
    Order_ID INT,
    Product_ID INT,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    Unit_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)
);

Key Design Decisions

• Parent_Category_ID: Self-referencing FK for subcategories (clothing → men → shirts).
• Order_Items composite PK: Ensures a product appears once per order.
• Unit_Price in Order_Items: Product prices change; the order must store the price at the time of purchase.
• Separate Orders table: An order is an event; its items are in a child table (normalized).

---

Case Study 3: Hospital Management System

Requirements

• Patients are admitted to rooms and treated by doctors.
• Each patient has a record of diagnoses and treatments.
• Doctors have specializations and can be assigned to multiple patients.
• Rooms have a type (ICU, General, Private) and a status.

Entities

• Patient
• Doctor
• Room
• Appointment
• Treatment

Relational Schema

CREATE TABLE Patients (
    Patient_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    DOB DATE,
    Gender CHAR(1),
    Phone VARCHAR(20),
    Address TEXT
);

CREATE TABLE Doctors (
    Doctor_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Specialization VARCHAR(100),
    Phone VARCHAR(20)
);

CREATE TABLE Rooms (
    Room_ID INT PRIMARY KEY,
    Room_Type VARCHAR(50) CHECK (Room_Type IN ('ICU', 'General', 'Private')),
    Status VARCHAR(20) CHECK (Status IN ('Available', 'Occupied'))
);

CREATE TABLE Admissions (
    Admission_ID INT PRIMARY KEY,
    Patient_ID INT NOT NULL,
    Room_ID INT,
    Admission_Date DATE NOT NULL,
    Discharge_Date DATE,
    FOREIGN KEY (Patient_ID) REFERENCES Patients(Patient_ID),
    FOREIGN KEY (Room_ID) REFERENCES Rooms(Room_ID)
);

CREATE TABLE Appointments (
    Appointment_ID INT PRIMARY KEY,
    Patient_ID INT NOT NULL,
    Doctor_ID INT NOT NULL,
    Appointment_Date TIMESTAMP NOT NULL,
    Diagnosis TEXT,
    FOREIGN KEY (Patient_ID) REFERENCES Patients(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctors(Doctor_ID)
);

Key Design Decisions

• Admissions vs Appointments: Admissions track in-patient stays (room assignment). Appointments track doctor visits (outpatient).
• Discharge_Date is nullable: NULL means the patient is still admitted.
• Appointment links Patient and Doctor: The M:N relationship between patients and doctors.

---

Case Study 4: Social Media Platform

Requirements

• Users can create posts, like posts, and comment on posts.
• Users can follow other users.
• Each post has text content and optional media.
• Likes and comments are tracked with timestamps.

Entities

• User
• Post
• Like
• Comment
• Follow

Relational Schema

CREATE TABLE Users (
    User_ID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL,
    Display_Name VARCHAR(100),
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Posts (
    Post_ID INT PRIMARY KEY,
    User_ID INT NOT NULL,
    Content TEXT NOT NULL,
    Media_URL VARCHAR(500),
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (User_ID) REFERENCES Users(User_ID)
);

CREATE TABLE Likes (
    User_ID INT,
    Post_ID INT,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (User_ID, Post_ID),
    FOREIGN KEY (User_ID) REFERENCES Users(User_ID),
    FOREIGN KEY (Post_ID) REFERENCES Posts(Post_ID)
);

CREATE TABLE Comments (
    Comment_ID INT PRIMARY KEY,
    Post_ID INT NOT NULL,
    User_ID INT NOT NULL,
    Content TEXT NOT NULL,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (Post_ID) REFERENCES Posts(Post_ID),
    FOREIGN KEY (User_ID) REFERENCES Users(User_ID)
);

CREATE TABLE Follows (
    Follower_ID INT,
    Followee_ID INT,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (Follower_ID, Followee_ID),
    FOREIGN KEY (Follower_ID) REFERENCES Users(User_ID),
    FOREIGN KEY (Followee_ID) REFERENCES Users(User_ID),
    CHECK (Follower_ID != Followee_ID)
);

Key Design Decisions

• Likes composite PK: Prevents duplicate likes (a user can like a post only once).
• Follows self-referential: Both Follower and Followee reference Users.
• CHECK (Follower_ID != Followee_ID): A user cannot follow themselves.

---

Case Study 5: Banking System

Requirements

• Customers can open accounts (Savings, Current, Fixed Deposit).
• Accounts track balance and transaction history.
• Transactions are either credit (deposit) or debit (withdrawal).
• A transaction must be atomic — money is not lost during transfers.

Entities

• Customer
• Account
• Transaction

Relational Schema

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

CREATE TABLE Accounts (
    Account_Number BIGINT PRIMARY KEY,
    Customer_ID INT NOT NULL,
    Account_Type VARCHAR(20) CHECK (Account_Type IN ('Savings', 'Current', 'FD')),
    Balance DECIMAL(15,2) DEFAULT 0,
    Created_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Status VARCHAR(20) DEFAULT 'Active',
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
);

CREATE TABLE Transactions (
    Transaction_ID BIGINT PRIMARY KEY,
    Account_Number BIGINT NOT NULL,
    Transaction_Type VARCHAR(10) CHECK (Transaction_Type IN ('Credit', 'Debit')),
    Amount DECIMAL(15,2) NOT NULL CHECK (Amount > 0),
    Balance_Before DECIMAL(15,2),
    Balance_After DECIMAL(15,2),
    Description VARCHAR(500),
    Transaction_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (Account_Number) REFERENCES Accounts(Account_Number)
);

Key Design Decisions

• Balance_Before and Balance_After: Store the balance at transaction time for audit trail. Never calculate balance by scanning all transactions.
• Account_Number as BIGINT: Bank account numbers are long (12-16 digits).
• Transaction_Type + CHECK on Amount > 0: Debits are positive amounts; the application subtracts. This prevents negative amounts.
• Atomic transfers: A transfer = debit from Account A + credit to Account B, wrapped in a transaction.

---

Case Study 6: Food Delivery

Requirements

• Restaurants list menu items with prices.
• Customers place orders from one restaurant at a time.
• Orders have items, delivery address, and payment.
• Delivery partners are assigned to orders.

Entities

• Restaurant
• MenuItem
• Customer
• Order
• OrderItem
• DeliveryPartner
• Delivery

Relational Schema

CREATE TABLE Restaurants (
    Restaurant_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Address TEXT,
    Cuisine_Type VARCHAR(50),
    Rating DECIMAL(2,1) DEFAULT 0
);

CREATE TABLE Menu_Items (
    Item_ID INT PRIMARY KEY,
    Restaurant_ID INT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    Description TEXT,
    Price DECIMAL(10,2) NOT NULL,
    Is_Available BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (Restaurant_ID) REFERENCES Restaurants(Restaurant_ID)
);

CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Phone VARCHAR(20) NOT NULL,
    Address TEXT
);

CREATE TABLE Delivery_Partners (
    Partner_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Phone VARCHAR(20) NOT NULL,
    Vehicle_Type VARCHAR(20),
    Is_Available BOOLEAN DEFAULT TRUE
);

CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT NOT NULL,
    Restaurant_ID INT NOT NULL,
    Order_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Status VARCHAR(20) CHECK (Status IN ('Placed', 'Preparing', 'Picked', 'Delivered', 'Cancelled')),
    Total DECIMAL(12,2),
    Delivery_Address TEXT,
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
    FOREIGN KEY (Restaurant_ID) REFERENCES Restaurants(Restaurant_ID)
);

CREATE TABLE Order_Items (
    Order_ID INT,
    Item_ID INT,
    Quantity INT NOT NULL,
    Unit_Price DECIMAL(10,2),
    PRIMARY KEY (Order_ID, Item_ID),
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    FOREIGN KEY (Item_ID) REFERENCES Menu_Items(Item_ID)
);

CREATE TABLE Deliveries (
    Delivery_ID INT PRIMARY KEY,
    Order_ID INT UNIQUE NOT NULL,
    Partner_ID INT,
    Picked_At TIMESTAMP,
    Delivered_At TIMESTAMP,
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    FOREIGN KEY (Partner_ID) REFERENCES Delivery_Partners(Partner_ID)
);

Key Design Decisions

• Order_Items has Unit_Price: Menu prices change; the order price is recorded at order time.
• Deliveries has UNIQUE on Order_ID: One order has one delivery.
• Nullable Partner_ID: An order can be placed before a delivery partner is assigned.
• Delivery_Partners.Is_Available: Bit flag for the assignment algorithm.

---

General Design Principles

Principle	Why
Start with normalization	Eliminate redundancy first; denormalize later if needed
Use surrogate keys	Auto-increment INT/BIGINT; avoid natural keys
Always add timestamps	created_at, updated_at on every table
Store price at transaction time	Prices change; the historical price matters
Use CHECK constraints	Enforce business rules at the database level
Index foreign keys	Every FK should have an index for join performance
Never store calculated values	Except for audit/historical purposes
Plan for soft deletes	Add is_deleted or deleted_at column
Document your schema	Comments on tables and columns save months of confusion

---

Interview Deep Dive

---

Key Takeaways

• Every database design starts with understanding the problem requirements.
• Identify entities, attributes, and relationships before writing SQL.
• Use surrogate keys, index foreign keys, and enforce constraints.
• Store historical prices in order/transaction tables.
• Plan for audit trails in financial and medical systems.
• Soft deletes protect data in regulated domains.
• Normalize first, denormalize only when performance requires it.
• Six case studies cover the most common real-world database design scenarios.