Real-World Database Design Projects

# CHAPTER 29

Real-World Database Design Projects #

1. Introduction #

2. Learning Objectives #

• Architect an E-Commerce system handling products, users, and orders.

• Understand the complexity of tracking historical data (Pricing).

• Architect a SaaS (Software as a Service) multi-tenant schema.

• Architect a highly regulated Hospital Management System.

• Apply JSONB, Constraints, and Primary Keys effectively in the real world.

3. Project 1: The E-Commerce Platform #

-- 1. Users
CREATE TABLE users (
    user_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Products (Live Data)
CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    current_price NUMERIC(10, 2) NOT NULL CHECK (current_price >= 0),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0)
);

-- 3. Orders (The Invoice)
CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE RESTRICT,
    status VARCHAR(50) DEFAULT 'Pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Order Items (The Pivot Table with Frozen Historical Data!)
CREATE TABLE order_items (
    order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INT REFERENCES products(product_id) ON DELETE RESTRICT,
    quantity INT NOT NULL CHECK (quantity > 0),
    price_at_purchase NUMERIC(10, 2) NOT NULL, -- CRITICAL: Freezes the historical price!
    PRIMARY KEY (order_id, product_id)
);

4. Project 2: The Multi-Tenant SaaS App (e.g., Slack or Trello) #

-- 1. Organizations (The Tenants)
CREATE TABLE organizations (
    org_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    company_name VARCHAR(150) NOT NULL,
    subscription_tier VARCHAR(50) DEFAULT 'Free'
);

-- 2. Users (Belong to an Organization)
CREATE TABLE users (
    user_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    org_id INT REFERENCES organizations(org_id) ON DELETE CASCADE,
    email VARCHAR(150) UNIQUE NOT NULL,
    role VARCHAR(50) DEFAULT 'Member'
);

-- 3. Projects (Also strictly tied to the Organization!)
CREATE TABLE projects (
    project_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    org_id INT REFERENCES organizations(org_id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    settings JSONB -- Flexible NoSQL column for project configurations!
);

*(By requiring WHERE org_id = ? on every query, the backend ensures data never bleeds between companies).*

5. Project 3: Hospital Management System #

-- 1. Patients
CREATE TABLE patients (
    patient_id UUID DEFAULT gen_random_uuid() PRIMARY KEY, -- UUIDs for security!
    full_name VARCHAR(200) NOT NULL,
    date_of_birth DATE NOT NULL
);

-- 2. Doctors
CREATE TABLE doctors (
    doctor_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    specialty VARCHAR(100) NOT NULL
);

-- 3. Appointments (The Pivot Table handling the N:M Relationship)
CREATE TABLE appointments (
    appointment_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id UUID REFERENCES patients(patient_id) ON DELETE CASCADE,
    doctor_id UUID REFERENCES doctors(doctor_id) ON DELETE RESTRICT,
    appointment_time TIMESTAMPTZ NOT NULL,
    status VARCHAR(50) DEFAULT 'Scheduled',
    notes TEXT, -- Doctors can write long-form notes
    -- Prevent double booking the same patient at the same exact time!
    UNIQUE (patient_id, appointment_time) 
);

6. The Danger of Over-Normalization #

7. Mini Project: Auditing the E-Commerce Store #

-- Create the Audit Log Table
CREATE TABLE price_audit_log (
    log_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    old_price NUMERIC,
    new_price NUMERIC,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);
-- We would then write a BEFORE UPDATE Trigger on the `products` table 
-- to automatically insert data here!

8. Common Mistakes #

• Using Cascading Deletes on Critical Data: Notice in the E-Commerce schema, userid INT REFERENCES users(userid) ON DELETE RESTRICT. Why RESTRICT instead of CASCADE? If an admin accidentally deletes John's account, CASCADE would instantly delete all of John's financial orders, destroying the company's accounting records! Financial data should *never* cascade. Use RESTRICT to aggressively block the deletion.

9. Best Practices #

• Draw it First: Never start typing CREATE TABLE until you have physically drawn an ERD (Entity Relationship Diagram) on a whiteboard or piece of paper. Map out the 1:N and N:M links visually. If the drawing is confusing, the code will be a disaster.

10. Exercises #

1. 1. In the E-Commerce schema, why is there a priceatpurchase column inside the orderitems pivot table?

SELECT o.company_name, u.email, p.name AS project_name
FROM organizations o
JOIN users u ON o.org_id = u.org_id
JOIN projects p ON o.org_id = p.org_id
WHERE o.org_id = 1;

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Describe a business scenario where you would explicitly choose ON DELETE RESTRICT over ON DELETE CASCADE when defining a Foreign Key.

• Q: Walk me through the database schema you would design for a ride-sharing application (like Uber) involving Riders, Drivers, and Trips.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #