Chapter 17: Real-World Case Studies — Comprehensive Exercises in Product Delivery Management and Task Management
What you'll learn in this chapter
- Extract entities from business requirements (product delivery management), build an ER diagram, and design end-to-end through table definitions and expected queries
- Implement supertypes and subtypes (LOCATION → FACTORY / STORE)
- Design a task-management system that includes RBAC and self-reference (task dependencies)
Prerequisites
This builds on the content from Chapter 9: The Flow of Design through Chapter 16: OAuth/OIDC. This chapter is positioned as the capstone of this guide, integrating concepts learned across multiple chapters.
Case Study 1: A Product Delivery Management System
Requirements
Company A produces all of its products at three production factories and delivers them to about 70 stores.
- Each store sources products from a fixed factory
- Deliveries happen twice a day, before lunch and before dinner
- The business flow: a store orders → the factory produces → the factory delivers to the store → the store receives it
- Each factory has a defined delivery route, visiting the stores on that route in order
Step 1: Extract Entities
Key point: since both "factory" and "store" share the common attributes of a "location" (address, phone), design them with a supertype locations + subtypes factories / stores (see Chapter 12: E-R Diagrams, on supertypes).
Step 2: ER Diagram
Step 3: Table Definitions
Product delivery management (PostgreSQL)
-- Supertype: location
CREATE TABLE locations (
code VARCHAR(10) PRIMARY KEY,
name VARCHAR(100) NOT NULL,
address VARCHAR(200) NOT NULL,
phone VARCHAR(20),
location_type VARCHAR(20) NOT NULL CHECK (location_type IN ('factory', 'store'))
);
-- Subtype: factory
CREATE TABLE factories (
location_code VARCHAR(10) PRIMARY KEY REFERENCES locations(code),
production_capacity INTEGER NOT NULL,
operation_start_date DATE NOT NULL
);
-- Delivery route
CREATE TABLE delivery_routes (
route_number VARCHAR(10) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
factory_code VARCHAR(10) NOT NULL REFERENCES factories(location_code),
estimated_min INTEGER,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
-- Subtype: store
CREATE TABLE stores (
location_code VARCHAR(10) PRIMARY KEY REFERENCES locations(code),
route_number VARCHAR(10) NOT NULL REFERENCES delivery_routes(route_number),
delivery_order INTEGER NOT NULL,
delivery_time_slot VARCHAR(20) NOT NULL CHECK (delivery_time_slot IN ('morning', 'evening'))
);
CREATE INDEX idx_stores_route_order ON stores(route_number, delivery_order);
-- Product master
CREATE TABLE products (
code VARCHAR(20) PRIMARY KEY,
name VARCHAR(100) NOT NULL,
unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0),
lot_size INTEGER NOT NULL DEFAULT 1 CHECK (lot_size > 0),
shelf_life_days INTEGER NOT NULL CHECK (shelf_life_days > 0),
temperature_range VARCHAR(50),
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
-- Orders
CREATE TABLE orders (
order_number VARCHAR(20) PRIMARY KEY,
store_code VARCHAR(10) NOT NULL REFERENCES stores(location_code),
order_datetime TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
delivery_scheduled TIMESTAMPTZ NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'confirmed', 'in_production', 'shipped', 'delivered'))
);
CREATE INDEX idx_orders_store_date ON orders(store_code, order_datetime);
CREATE INDEX idx_orders_scheduled ON orders(delivery_scheduled);
CREATE TABLE order_items (
order_number VARCHAR(20) NOT NULL REFERENCES orders(order_number) ON DELETE CASCADE,
product_code VARCHAR(20) NOT NULL REFERENCES products(code) ON DELETE RESTRICT,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10, 2) NOT NULL, -- a snapshot
PRIMARY KEY (order_number, product_code)
);
-- Production
CREATE TABLE productions (
production_number VARCHAR(20) PRIMARY KEY,
factory_code VARCHAR(10) NOT NULL REFERENCES factories(location_code),
production_date DATE NOT NULL,
shift VARCHAR(20) NOT NULL CHECK (shift IN ('morning', 'evening')),
scheduled_completion TIMESTAMPTZ NOT NULL,
actual_completion TIMESTAMPTZ,
status VARCHAR(20) NOT NULL DEFAULT 'planned'
CHECK (status IN ('planned', 'in_progress', 'completed'))
);
CREATE TABLE production_items (
production_number VARCHAR(20) NOT NULL REFERENCES productions(production_number) ON DELETE CASCADE,
product_code VARCHAR(20) NOT NULL REFERENCES products(code),
planned_quantity INTEGER NOT NULL CHECK (planned_quantity > 0),
actual_quantity INTEGER CHECK (actual_quantity IS NULL OR actual_quantity >= 0),
lot_number VARCHAR(50),
PRIMARY KEY (production_number, product_code)
);
-- Delivery
CREATE TABLE deliveries (
delivery_number VARCHAR(20) PRIMARY KEY,
route_number VARCHAR(10) NOT NULL REFERENCES delivery_routes(route_number),
delivery_date DATE NOT NULL,
vehicle_number VARCHAR(20),
driver_name VARCHAR(50),
departure_time TIMESTAMPTZ,
return_time TIMESTAMPTZ,
status VARCHAR(20) NOT NULL DEFAULT 'preparing'
CHECK (status IN ('preparing', 'in_transit', 'completed'))
);
CREATE TABLE delivery_items (
delivery_number VARCHAR(20) NOT NULL REFERENCES deliveries(delivery_number) ON DELETE CASCADE,
store_code VARCHAR(10) NOT NULL REFERENCES stores(location_code),
product_code VARCHAR(20) NOT NULL REFERENCES products(code),
quantity INTEGER NOT NULL CHECK (quantity > 0),
delivered_quantity INTEGER CHECK (delivered_quantity IS NULL OR delivered_quantity >= 0),
delivery_time TIMESTAMPTZ,
receipt_status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (receipt_status IN ('pending', 'delivered', 'rejected')),
PRIMARY KEY (delivery_number, store_code, product_code)
);
Step 4: An Expected Query
Aggregating production instructions (orders → required lot counts by factory and product)
SELECT
o.delivery_scheduled::date AS production_date,
dr.factory_code,
oi.product_code,
p.name AS product_name,
p.lot_size,
SUM(oi.quantity) AS total_quantity,
CEILING(SUM(oi.quantity)::numeric / p.lot_size) AS required_lots
FROM orders o
JOIN order_items oi ON o.order_number = oi.order_number
JOIN stores s ON o.store_code = s.location_code
JOIN delivery_routes dr ON s.route_number = dr.route_number
JOIN products p ON oi.product_code = p.code
WHERE o.status IN ('pending', 'confirmed')
GROUP BY o.delivery_scheduled::date, dr.factory_code, oi.product_code, p.name, p.lot_size
ORDER BY production_date, dr.factory_code, oi.product_code;
This query produces "aggregating tomorrow's lunchtime-delivery orders, how many lots of which product Factory 1 should produce." Just by joining the orders table, delivery routes, and the product master, you can derive a business-level "production instruction."
The benefit of a supertype / subtype design
Because this case study places a locations supertype, a cross-cutting query like "a list of all locations" (SELECT * FROM locations) is easy to write. Meanwhile, "attributes specific to factories" (production capacity) live only in the factories table, avoiding data bloat.
Case Study 2: A Task-Management System
Requirements
- Users can register and log in
- A project can be created, and members can be invited
- Tasks can be created, edited, and assigned within a project
- A task has a priority, a due date, and a status
- Comments can be added to a task
- A task can express a dependency — "can't start until a prerequisite task is finished"
Step 1: ER Diagram
Step 2: Table Definitions
Task management (PostgreSQL)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
display_name VARCHAR(100) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
created_by INTEGER NOT NULL REFERENCES users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE project_members (
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role VARCHAR(20) NOT NULL DEFAULT 'member'
CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
joined_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (project_id, user_id)
);
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
title VARCHAR(200) NOT NULL,
description TEXT,
created_by INTEGER NOT NULL REFERENCES users(id),
assigned_to INTEGER REFERENCES users(id),
status VARCHAR(20) NOT NULL DEFAULT 'todo'
CHECK (status IN ('todo', 'in_progress', 'review', 'done', 'cancelled')),
priority INTEGER NOT NULL DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
due_date DATE,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);
CREATE INDEX idx_tasks_assigned ON tasks(assigned_to, status);
CREATE INDEX idx_tasks_due ON tasks(due_date) WHERE status NOT IN ('done', 'cancelled');
-- Task dependencies (self-referencing many-to-many)
CREATE TABLE task_dependencies (
dependent_task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
prerequisite_task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (dependent_task_id, prerequisite_task_id),
CHECK (dependent_task_id <> prerequisite_task_id) -- can't depend on itself
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
task_id INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
content TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_comments_task ON comments(task_id, created_at);
Step 3: An Expected Query
A list of startable tasks (where every prerequisite task is complete)
SELECT t.*
FROM tasks t
WHERE t.assigned_to = $1
AND t.status = 'todo'
AND NOT EXISTS (
-- Exclude if even one prerequisite task is incomplete
SELECT 1
FROM task_dependencies td
JOIN tasks pre ON pre.id = td.prerequisite_task_id
WHERE td.dependent_task_id = t.id
AND pre.status NOT IN ('done', 'cancelled')
)
ORDER BY t.priority, t.due_date NULLS LAST;
This query pulls out, among the tasks assigned to the currently logged-in user ($1) that are in the todo state, only those where "every prerequisite task is finished," ordered by priority and due date.
Detecting circular task dependencies
If a user can freely create rows in task_dependencies, a circular dependency like "A → B → C → A" could get registered. Preventing cycles at the database level is complex, so it's common to detect it at the application layer before insertion (using DFS or a topological sort).
Exercises
Exercise: Comprehensive design for a library management system
Using the techniques covered in this guide, build an ER diagram, table definitions, and one expected query from the following requirements.
Requirements:
- A member can borrow a book (a loan)
- A book can have multiple authors
- Books have categories (a hierarchical structure)
- A loan has a due date and a late fee
- A member can reserve a book (if it's currently on loan)
- Among members, staff (librarians) have system-administration permissions (RBAC)
Expected query: "List the currently loaned-out books that are past their due date, with the member's name, book title, and days overdue"
Sample answer
ER diagram (simplified):
Table definitions (main parts):
CREATE TABLE members (
id SERIAL PRIMARY KEY,
member_number VARCHAR(20) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INTEGER REFERENCES categories(id)
);
CREATE TABLE books (
id SERIAL PRIMARY KEY,
isbn VARCHAR(13) UNIQUE,
title VARCHAR(500) NOT NULL,
publisher VARCHAR(200),
category_id INTEGER NOT NULL REFERENCES categories(id),
total_copies INTEGER NOT NULL CHECK (total_copies >= 0),
available_copies INTEGER NOT NULL CHECK (available_copies >= 0)
);
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL
);
CREATE TABLE book_authors (
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
author_order INTEGER NOT NULL DEFAULT 1,
PRIMARY KEY (book_id, author_id)
);
CREATE TABLE loans (
id SERIAL PRIMARY KEY,
member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
loan_date DATE NOT NULL DEFAULT CURRENT_DATE,
due_date DATE NOT NULL,
return_date DATE,
fine_amount NUMERIC(10, 2) DEFAULT 0 CHECK (fine_amount >= 0)
);
CREATE INDEX idx_loans_member_unreturned ON loans(member_id) WHERE return_date IS NULL;
CREATE INDEX idx_loans_due_unreturned ON loans(due_date) WHERE return_date IS NULL;
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
reservation_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) NOT NULL DEFAULT 'waiting'
CHECK (status IN ('waiting', 'ready', 'fulfilled', 'cancelled'))
);
-- RBAC
CREATE TABLE roles (
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE member_roles (
member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
granted_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (member_id, role_id)
);
Expected query:
A list of overdue loans
SELECT
m.name AS member_name,
b.title AS book_title,
l.loan_date,
l.due_date,
CURRENT_DATE - l.due_date AS overdue_days
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books b ON b.id = l.book_id
WHERE l.return_date IS NULL
AND l.due_date < CURRENT_DATE
ORDER BY overdue_days DESC;
Common mistakes:
- Automatically updating
books.available_copieswith a trigger: the lock-contention risk covered in Chapter 14. RunUPDATE books SET available_copies = available_copies - 1in the same transaction as theINSERT/UPDATEtoloans, at the application layer - Making
book_authors's primary key a standalone ID instead of(book_id, author_id): this would let the same author be registered on the same book twice. Use a composite primary key to prevent duplicates - Holding the late fee on
members.fine_balance: since it can be derived by computing from individual loans, aggregate it withSUMoverloans.fine_amount
Alternative approach: you could also make loans's primary key the composite (member_id, book_id, loan_date), but a surrogate key (id SERIAL) is recommended since it's easier to reference as a foreign key.
Summary
Here's a recap of what this chapter covered.
- Practiced the full flow of requirements → entity extraction → ER diagram → table definitions → expected queries with two case studies
- Product delivery management: an example implementation of supertypes and subtypes (locations → factories / stores)
- Task management: combining self-referencing many-to-many (task_dependencies) with RBAC
- The principle is to derive aggregates from normalized tables via
JOIN(don't casually keep an aggregate column) - A partial index (
WHERE return_date IS NULL, etc.) speeds things up by excluding unnecessary rows
In the final chapter, we work through additional exercises to deepen your understanding of the normalization covered in Chapter 11.
Next chapter