Design a Hospital Management Database

Design a normalized PostgreSQL hospital database with constraints and indexes.

Introduction

30 Second Summary

Every time you book a doctor's appointment, check into a hospital, or pick up a prescription, a database is enforcing rules behind the scenes. It ensures your appointment time is valid, your doctor actually exists, and your prescription dates make sense.

In this project, you will design and build a hospital management database in PostgreSQL from scratch. You will create tables with strict constraints, load them with 50,000+ rows of realistic data, and then diagnose and fix a slow query using EXPLAIN ANALYZE and targeted indexes.

What You'll Build

A working hospital database where you can query which doctors are busiest, attempt to insert invalid data and watch PostgreSQL reject it every time, and run a slow query, add an index, and watch the execution time drop dramatically.

By the end of this project, you'll have:

  • A fully normalized database schema with 5 interconnected tables that reject invalid data the moment you try to insert it.
  • A set of realistic multi-table queries that answer real hospital questions like which departments handle the most appointments and which patients have the most prescriptions.
  • Hands-on experience using EXPLAIN ANALYZE to diagnose a slow query and fix it with targeted indexes, proving the performance improvement with before-and-after numbers.
  • Secret Mission: Add an EXCLUDE constraint that makes appointment double-booking physically impossible at the database level.

Are there any prerequisites?

You should be comfortable with basic SQL commands like CREATE TABLE, SELECT, INSERT, and JOIN. You also need PostgreSQL already installed on your machine.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up the Hospital Database

Your hospital schema needs its own dedicated database. PostgreSQL can host many databases on a single server, so creating a separate one keeps your work isolated and easy to tear down later without affecting anything else on your system.

In this step, you'll confirm PostgreSQL is running, create a fresh database named hospital, and connect to it with the psql command-line client.

In this step, get ready to:
  • Verify PostgreSQL is running on your machine.
  • Create the hospital database.
  • Connect and confirm you're in the right database.
Verify PostgreSQL is running

Before you create anything, you need to confirm the PostgreSQL server process is active and accepting connections.

  • Open your terminal in VS Code by pressing Ctrl+` (macOS and Windows).
  • Check which version of psql is available by running:
psql --version

You should see a version string like psql (PostgreSQL) 18.4 or similar.

  • Check whether the PostgreSQL service is running by running:
sudo systemctl status postgresql

✔️ I see "active (running)"

Your PostgreSQL service is running and ready to accept connections. Move on to the next substep.

ⓧ The service is not active

The service needs to be started. Start it by running:

sudo systemctl start postgresql

Then re-check the status to confirm it says active (running).

sudo systemctl status postgresql

Still not seeing PostgreSQL?

If systemctl reports the service failed to start, check the logs with sudo journalctl -u postgresql --no-pager -n 20 for details.

Help me troubleshoot PostgreSQL startup

Create the hospital database

Each PostgreSQL server can hold many databases. You'll create one named hospital so all your tables live in their own isolated space.

  • Create the database by running:
sudo -u postgres createdb hospital
  • Connect to the new database by running:
sudo -u postgres psql -d hospital

Your terminal prompt should change to hospital=# indicating you are now inside the psql client, connected to the hospital database.

Direct access without sudo

If your Linux user already has PostgreSQL peer authentication configured, you can use createdb hospital and psql -d hospital directly without the sudo -u postgres prefix.

Seeing a "database already exists" error?

If you previously created a database named hospital, you can drop it and start fresh by running sudo -u postgres dropdb hospital and then re-running the createdb command above.

Help me fix my database creation error

Confirm your connection

You should now be inside psql, connected to the hospital database. Before you run the next command, what two pieces of information do you expect it to return?

  • Run this query inside psql:
SELECT current_database(), version();

You should see hospital as the database name and your PostgreSQL version string in the output.

What does this query do?

current_database() returns the name of the database your session is connected to. version() returns the full PostgreSQL server version. Together they confirm you're in the right place.

Seeing a different database name?

If current_database() returns postgres instead of hospital, you connected to the default database. Type \q to quit, then reconnect with sudo -u postgres psql -d hospital.

Help me switch to the hospital database

Your database is ready and waiting for tables. Next up, you'll design the core tables with strict constraints that reject bad data before it ever gets stored.

Create Core Tables with Constraints

Your database exists, but right now it is an empty shell. A hospital tracks departments, doctors, and patients. Each of those entities has rules: floors cannot be negative, emails must be unique, and gender values come from a fixed set.

In this step, you will translate those real-world rules into PostgreSQL tables with strict data types and CHECK constraints. The database itself will reject bad data before it ever gets stored.

In this step, get ready to:
  • Create three core tables (departments, doctors, patients) with constraints that enforce business rules.
  • Insert sample data and verify it with a JOIN query.
  • Test that invalid data is rejected by your constraints.
Create the departments, doctors, and patients tables

You will define each table directly in your psql session so you get immediate feedback after each one.

  • Create the departments table by pasting this SQL into your psql prompt:
-- Departments
CREATE TABLE departments (
    department_id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    building TEXT NOT NULL,
    floor INTEGER NOT NULL CHECK (floor >= 0)
);

What does this code do?

  • BIGSERIAL creates an auto-incrementing 8-byte integer for the primary key. Each new row gets the next number automatically.
  • TEXT NOT NULL UNIQUE on name means every department must have a name, and no two departments can share the same one.
  • CHECK (floor >= 0) prevents negative floor numbers. The database will reject any INSERT that violates this rule.
  • You should see CREATE TABLE printed in your terminal. Confirm the structure by running:
\d departments

You should see a table with four columns: department_id, name, building, and floor. The indexes section lists the primary key and the unique constraint on name.

Seeing a syntax error?

  • Make sure the statement ends with a semicolon. Without it, psql waits for more input instead of executing.
  • Check that parentheses are balanced. A missing closing ) will cause a syntax error.
  • help me fix a CREATE TABLE syntax error in psql
  • Create the doctors table by pasting this SQL:
-- Doctors
CREATE TABLE doctors (
    doctor_id BIGSERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    specialization TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    department_id BIGINT NOT NULL REFERENCES departments(department_id),
    hired_at DATE NOT NULL DEFAULT CURRENT_DATE
);

What does this code do?

  • REFERENCES departments(department_id) creates a foreign key. Every doctor must belong to a department that actually exists. PostgreSQL will reject any doctor row that points to a non-existent department.
  • email TEXT UNIQUE NOT NULL ensures no two doctors share the same email address.
  • phone TEXT has no NOT NULL constraint because not every doctor record has a phone number on file.
  • Confirm you see CREATE TABLE printed. Verify the structure:
\d doctors

Look for the Foreign-key constraints section at the bottom. It confirms that department_id references the departments table.

Getting a 'relation departments does not exist' error?

  • The doctors table references departments, so departments must exist first. Run \dt to check if the departments table is listed.
  • If it is missing, scroll up and create it first, then retry the doctors table.
  • help me fix a foreign key reference error in PostgreSQL
  • Create the patients table by pasting this SQL:
-- Patients
CREATE TABLE patients (
    patient_id BIGSERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth DATE NOT NULL,
    gender TEXT NOT NULL CHECK (gender IN ('male', 'female', 'other')),
    phone TEXT,
    email TEXT,
    registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

What does this code do?

  • CHECK (gender IN ('male', 'female', 'other')) restricts the gender column to exactly three allowed values. Any other string is rejected.
  • TIMESTAMPTZ stores a time-zone-aware timestamp. This is the recommended type for recording when events happened across different time zones.
  • DEFAULT NOW() automatically fills in the registration timestamp if you do not provide one.
  • Confirm you see CREATE TABLE printed. Verify all three tables are now in the database:
\dt

You should see departments, doctors, and patients listed as tables in the public schema.

Missing a table from the list?

  • If a table is not shown, its CREATE TABLE likely failed silently due to a typo. Re-paste the SQL for the missing table.
  • If you see an error about the table already existing, it was actually created. The \dt output is the definitive check.
  • help me figure out why my table is not appearing in psql

Why TEXT instead of VARCHAR?

PostgreSQL stores TEXT and VARCHAR identically under the hood. Using TEXT with a CHECK constraint for length limits (if needed) is the recommended pattern because it is clearer and easier to modify later.

  • Now save your schema to a file. In a separate terminal (not psql), create schema.sql in your project directory by opening it in VS Code.
  • Paste the three CREATE TABLE statements you just ran into schema.sql and save the file. Add a header comment at the top:
-- Hospital Management Database Schema
-- Designed for PostgreSQL 14+

This file serves as a reference for your complete schema. You already ran these statements in psql, so the tables exist in your database.

✔️ Awesome, I've got everything!

Great. Make sure you have saved schema.sql with all three CREATE TABLE statements.

ⓧ I'd like to double check the full code

Your schema.sql should contain these three sections in order:

  • The header comments (-- Hospital Management Database Schema and -- Designed for PostgreSQL 14+).
  • The departments CREATE TABLE block (4 columns: department_id, name, building, floor).
  • The doctors CREATE TABLE block (8 columns: doctor_id through hired_at).
  • The patients CREATE TABLE block (8 columns: patient_id through registered_at).

Scroll up and compare each CREATE TABLE chunk to what you pasted into the file. Every column name, constraint, and data type should match exactly.

Insert sample data and verify with a query

Tables with no data in them are hard to test. You need real rows to confirm your schema works and to practice queries in later steps.

  • Insert five departments by running this in psql:
-- Seed departments
INSERT INTO departments (name, building, floor) VALUES
    ('Cardiology', 'Building A', 2),
    ('Neurology', 'Building A', 3),
    ('Orthopedics', 'Building B', 1),
    ('Pediatrics', 'Building C', 1),
    ('Emergency', 'Building A', 0);

What does this code do?

This inserts five hospital departments. Notice that department_id is not listed in the column list. Because it is BIGSERIAL, PostgreSQL auto-generates the IDs (1 through 5) for you.

The Emergency department has floor 0 (ground floor), which passes the CHECK (floor >= 0) constraint.

  • You should see INSERT 0 5 confirming all five rows were inserted. Verify by running:
SELECT * FROM departments;

You should see five rows with auto-generated IDs from 1 to 5.

  • Insert six doctors by running this in psql:
-- Seed doctors
INSERT INTO doctors (first_name, last_name, specialization, email, phone, department_id) VALUES
    ('Sarah', 'Chen', 'Interventional Cardiology', 'sarah.chen@hospital.org', '555-0101', 1),
    ('James', 'Wilson', 'Electrophysiology', 'james.wilson@hospital.org', '555-0102', 1),
    ('Maria', 'Garcia', 'Neurosurgery', 'maria.garcia@hospital.org', '555-0103', 2),
    ('David', 'Kim', 'Sports Medicine', 'david.kim@hospital.org', '555-0104', 3),
    ('Emily', 'Taylor', 'Neonatology', 'emily.taylor@hospital.org', '555-0105', 4),
    ('Robert', 'Johnson', 'Trauma Surgery', 'robert.johnson@hospital.org', '555-0106', 5);

What does this code do?

Each doctor references a department_id that exists in the departments table. Sarah Chen and James Wilson both belong to department 1 (Cardiology). The foreign key constraint guarantees that every department_id here is valid.

  • You should see INSERT 0 6 printed. Now verify the relationship between doctors and departments with a JOIN query:
SELECT d.first_name, d.last_name, d.specialization, dep.name AS department
FROM doctors d
JOIN departments dep ON dep.department_id = d.department_id;

You should see six rows, each showing a doctor alongside their department name. Sarah Chen and James Wilson both appear under Cardiology.

Seeing zero rows or an error?

  • If the query returns zero rows, the doctors INSERT may have failed. Run SELECT COUNT(*) FROM doctors; to check.
  • If you see an error about a missing column, double-check that your departments table has a column named exactly department_id (not id or dept_id).
  • help me debug my JOIN query that returns no results
  • Save these INSERT statements to a new file called seed.sql in VS Code for reference.

✔️ Awesome, I've got everything!

Great. Make sure seed.sql is saved with both INSERT blocks.

ⓧ I'd like to double check the full code

-- Seed departments
INSERT INTO departments (name, building, floor) VALUES
    ('Cardiology', 'Building A', 2),
    ('Neurology', 'Building A', 3),
    ('Orthopedics', 'Building B', 1),
    ('Pediatrics', 'Building C', 1),
    ('Emergency', 'Building A', 0);

-- Seed doctors
INSERT INTO doctors (first_name, last_name, specialization, email, phone, department_id) VALUES
    ('Sarah', 'Chen', 'Interventional Cardiology', 'sarah.chen@hospital.org', '555-0101', 1),
    ('James', 'Wilson', 'Electrophysiology', 'james.wilson@hospital.org', '555-0102', 1),
    ('Maria', 'Garcia', 'Neurosurgery', 'maria.garcia@hospital.org', '555-0103', 2),
    ('David', 'Kim', 'Sports Medicine', 'david.kim@hospital.org', '555-0104', 3),
    ('Emily', 'Taylor', 'Neonatology', 'emily.taylor@hospital.org', '555-0105', 4),
    ('Robert', 'Johnson', 'Trauma Surgery', 'robert.johnson@hospital.org', '555-0106', 5);
Test your constraints with invalid data

Your tables have rules, but you have not proven they actually work yet. The best way to trust your constraints is to deliberately break them and watch PostgreSQL refuse.

Before you run each of these, predict whether it will succeed or fail. What rule does each one violate?

  • Try inserting a department with a negative floor:
INSERT INTO departments (name, building, floor) VALUES ('Fake', 'X', -1);

PostgreSQL rejects it with a CHECK constraint violation. The floor >= 0 rule made it impossible to store a negative floor number.

  • Try inserting a doctor with a duplicate email:
INSERT INTO doctors (first_name, last_name, specialization, email, phone, department_id)
VALUES ('Fake', 'Doctor', 'Testing', 'sarah.chen@hospital.org', '555-0000', 1);

PostgreSQL rejects it with a unique constraint violation. The email sarah.chen@hospital.org already exists in the table.

  • Try inserting a patient with an invalid gender value:
INSERT INTO patients (first_name, last_name, date_of_birth, gender)
VALUES ('Test', 'Patient', '1990-01-01', 'invalid');

PostgreSQL rejects it with a CHECK constraint violation. Only male, female, and other are allowed.

Why enforce rules at the database level?

Application code can have bugs. Multiple applications can write to the same database. By putting these rules in the schema, invalid data is physically impossible to store regardless of which tool or application writes to the database.

This is the difference between validation (which can be bypassed) and a constraint (which cannot).

One of the inserts succeeded when it should have failed?

  • If the negative floor INSERT succeeded, your departments table is missing the CHECK (floor >= 0) constraint. Drop and recreate the table with the constraint included.
  • If the duplicate email INSERT succeeded, check that your doctors table has UNIQUE on the email column. Run \d doctors to inspect.
  • help me figure out why my CHECK constraint is not rejecting invalid data

Your schema now has three tables with strict rules that make bad data impossible. Next up, you will add the appointments and prescriptions tables to link these entities together with foreign keys.

Add Relationships and Referential Integrity

Your departments, doctors, and patients tables each hold valid data on their own. But a hospital does not work in isolation. Appointments connect patients to doctors, and prescriptions tie back to both.

Without foreign keys, nothing stops you from inserting an appointment for a patient who does not exist. In this step, you will create the two remaining tables and let PostgreSQL enforce that every relationship points to a real record.

In this step, get ready to:
  • Create the appointments table with foreign keys, temporal columns, and a CHECK constraint.
  • Create the prescriptions table with an optional appointment link.
  • Test referential integrity by attempting invalid inserts and watching PostgreSQL reject them.
Create the appointments table

Appointments are the core scheduling unit in a hospital. Each appointment links one patient to one doctor at a specific time window.

  • In your psql session, create the appointments table by running this SQL:
-- Appointments (links patients to doctors with temporal data)
CREATE TABLE appointments (
    appointment_id BIGSERIAL PRIMARY KEY,
    patient_id BIGINT NOT NULL REFERENCES patients(patient_id),
    doctor_id BIGINT NOT NULL REFERENCES doctors(doctor_id),
    scheduled_start TIMESTAMP NOT NULL,
    scheduled_end TIMESTAMP NOT NULL,
    status TEXT NOT NULL DEFAULT 'scheduled'
        CHECK (status IN ('scheduled', 'completed', 'cancelled', 'no_show')),
    notes TEXT,
    CHECK (scheduled_end > scheduled_start)
);

What does this table do?

This table introduces two kinds of protection at once:

  • Foreign keys (REFERENCES patients(patient_id) and REFERENCES doctors(doctor_id)) guarantee every appointment points to a real patient and a real doctor.
  • The CHECK (scheduled_end > scheduled_start) constraint makes it impossible to store a time window that ends before it begins.
  • The status column is restricted to four known values so no typo or invalid state can sneak in.

You should see CREATE TABLE printed in your psql session, confirming the table was created.

Seeing a "relation does not exist" error?

  • Make sure the patients and doctors tables already exist. The foreign keys reference them, so they must be created first.
  • Confirm you are connected to the hospital database by running SELECT current_database(); in psql.

Help me troubleshoot my CREATE TABLE error.

Create the prescriptions table

Prescriptions connect a medication order to a patient and doctor. Some prescriptions come from an appointment, but others (like repeat prescriptions) happen outside of one.

  • Create the prescriptions table by running this SQL in psql:
-- Prescriptions (links to patient, doctor, and optionally an appointment)
CREATE TABLE prescriptions (
    prescription_id BIGSERIAL PRIMARY KEY,
    patient_id BIGINT NOT NULL REFERENCES patients(patient_id),
    doctor_id BIGINT NOT NULL REFERENCES doctors(doctor_id),
    appointment_id BIGINT REFERENCES appointments(appointment_id),
    medication TEXT NOT NULL,
    dosage TEXT NOT NULL,
    frequency TEXT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    CHECK (end_date IS NULL OR end_date >= start_date)
);

Why is appointment_id nullable?

Notice that appointment_id has no NOT NULL constraint. This means a prescription can exist without linking to a specific appointment. Real hospitals issue repeat prescriptions that are not tied to a single visit.

The CHECK (end_date IS NULL OR end_date >= start_date) constraint allows open-ended prescriptions (where end_date is NULL) while ensuring that if an end date is specified, it cannot be before the start date.

  • Confirm all five tables exist by running:
\dt

You should see departments, doctors, patients, appointments, and prescriptions listed in the output.

Only seeing three tables?

  • Check that each CREATE TABLE statement printed CREATE TABLE as confirmation. If one failed silently, scroll up in your terminal to find the error.
  • If you accidentally created a table with a typo, drop it with DROP TABLE tablename; and re-run the correct CREATE TABLE statement.

Help me figure out why my tables are not showing up.

✔️ Awesome, I've got everything!

Great. Double check you have all five tables by confirming \dt shows departments, doctors, patients, appointments, and prescriptions.

ⓧ I'd like to double check the full code

Your schema.sql should now contain these five CREATE TABLE statements in order:

  • departments (from Step 2).
  • doctors (from Step 2).
  • patients (from Step 2).
  • appointments (created in this step).
  • prescriptions (created in this step).

Scroll up and confirm each CREATE TABLE block matches what you ran. The order matters because later tables reference earlier ones via foreign keys.

Test referential integrity

Your tables have foreign keys, but you have not seen them in action yet. The real test is trying to insert data that violates the rules and watching PostgreSQL block it.

  • First, insert a test patient so you have valid data to reference later:
-- Insert a test patient for referential integrity testing
INSERT INTO patients (first_name, last_name, date_of_birth, gender)
VALUES ('Alice', 'Test', '1990-05-15', 'female');

You should see INSERT 0 1 confirming the patient was added.

Now for the real test. Before you run the next command, think about what you expect to happen. You are about to insert an appointment referencing patient_id 9999. Does patient 9999 exist?

  • Try to insert an appointment for a non-existent patient by running:
INSERT INTO appointments (patient_id, doctor_id, scheduled_start, scheduled_end, status)
VALUES (9999, 1, '2026-08-01 09:00', '2026-08-01 09:30', 'scheduled');

PostgreSQL rejects this with a foreign key violation. Patient 9999 does not exist, so the database refuses to create a dangling reference.

What just happened?

The foreign key on patient_id checked whether the value 9999 exists in the patients table. It does not, so PostgreSQL raised an error. This is referential integrity in action. No application code required.

  • Next, test the CHECK constraint by trying to schedule an appointment where the end time is before the start time:
INSERT INTO appointments (patient_id, doctor_id, scheduled_start, scheduled_end, status)
VALUES (1, 1, '2026-08-01 09:30', '2026-08-01 09:00', 'scheduled');

PostgreSQL rejects this too. The CHECK (scheduled_end > scheduled_start) constraint caught that 09:00 is not after 09:30.

  • Finally, insert a valid appointment to confirm that correct data is accepted:
INSERT INTO appointments (patient_id, doctor_id, scheduled_start, scheduled_end, status)
VALUES (1, 1, '2026-08-01 09:00', '2026-08-01 09:30', 'scheduled');

This time you see INSERT 0 1. Patient 1 exists, doctor 1 exists, and the time window is valid. All constraints pass.

Why enforce rules at the database level?

Application code can have bugs or be bypassed entirely (direct SQL access, migration scripts, a second app). Constraints at the database level are the last line of defense. They guarantee data integrity regardless of how the data enters the system.

Your schema is now five tables strong, and every relationship is enforced by the database itself. Next up, you will load thousands of rows of realistic data and write queries that span all five tables.

Seed Data and Write Queries

Your schema is solid. Every table has constraints, every relationship has referential integrity, and invalid data gets rejected. But right now the database is nearly empty.

An empty database hides performance problems. Real hospitals have thousands of patients and tens of thousands of appointments. In this step, you will load 50,000+ rows of realistic data, write multi-table queries, and discover that some queries are painfully slow without optimization.

In this step, get ready to:
  • Generate 500 patients, 50,000 appointments, and 5,000 prescriptions using generate_series.
  • Write and run three analytical queries with JOINs, filtering, and aggregation.
  • Run a date-range query and observe how slow it is on 50,000 unindexed rows.
Seed 500 patients with generate_series

PostgreSQL's generate_series function produces a sequence of numbers you can feed into INSERT statements. This lets you create hundreds or thousands of rows in a single query without writing them by hand.

  • In your psql session (still connected to the hospital database), paste the following to generate 500 patients with cycling last names:
-- Seed 500 patients with varied last names
INSERT INTO patients (first_name, last_name, date_of_birth, gender, phone, email, registered_at)
SELECT
    'Patient' || n,
    CASE (n % 5)
        WHEN 0 THEN 'Smith'
        WHEN 1 THEN 'Johnson'
        WHEN 2 THEN 'Williams'
        WHEN 3 THEN 'Brown'
        WHEN 4 THEN 'Jones'
    END,
    DATE '1940-01-01' + (floor(random() * 30000))::INTEGER,
    CASE (n % 3) WHEN 0 THEN 'male' WHEN 1 THEN 'female' ELSE 'other' END,
    '555-' || LPAD(n::TEXT, 4, '0'),
    'patient' || n || '@email.com',
    NOW() - (random() * INTERVAL '730 days')
FROM generate_series(1, 500) AS n;

What does this code do?

generate_series(1, 500) produces numbers 1 through 500. Each number becomes one patient row.

The CASE (n % 5) expression cycles last names through Smith, Johnson, Williams, Brown, and Jones. This means roughly 100 patients will have each last name, giving you meaningful result sets when you query by name later.

  • Confirm the patients were inserted by running:
SELECT COUNT(*) FROM patients;

You should see 500 in the output.

Seeing an error instead of 500?

  • If you see a CHECK constraint violation on gender, make sure the CASE expression uses n % 3 (not n % 5) for the gender column.
  • If you see a duplicate key error, the patients table may already have data from a previous attempt. Run TRUNCATE patients CASCADE; first, then try again.

Help me debug my patient seed query in PostgreSQL.

Seed 50,000 appointments across two years

Appointments are the core of a hospital's operational data. 50,000 rows is large enough to expose performance differences between indexed and unindexed queries, but small enough to seed in under a second.

  • Paste the following to generate appointments spread across 2024-2025:
-- Seed 50,000 appointments spread across 2 years
INSERT INTO appointments (patient_id, doctor_id, scheduled_start, scheduled_end, status)
SELECT
    1 + floor(random() * 500)::INTEGER,
    1 + floor(random() * 6)::INTEGER,
    ts,
    ts + INTERVAL '30 minutes',
    CASE (n % 4)
        WHEN 0 THEN 'scheduled'
        WHEN 1 THEN 'completed'
        WHEN 2 THEN 'completed'
        WHEN 3 THEN 'cancelled'
    END
FROM (
    SELECT
        n,
        TIMESTAMP '2024-01-01' + (floor(random() * 730))::INTEGER * INTERVAL '1 day'
            + (8 + floor(random() * 9)::INTEGER) * INTERVAL '1 hour' AS ts
    FROM generate_series(1, 50000) AS n
) sub;

What does this code do?

  • The subquery generates 50,000 timestamps starting from January 2024, randomly distributed across 730 days (2 years) between 8 AM and 5 PM.
  • Each appointment is 30 minutes long (ts + INTERVAL '30 minutes'), satisfying the CHECK constraint that scheduled_end > scheduled_start.
  • Status cycles through scheduled, completed, completed, and cancelled. Half of all appointments end up as 'completed', which gives you plenty of data for the prescriptions table next.
  • Confirm the row count by running:
SELECT COUNT(*) FROM appointments;

You should see 50000.

Seed 5,000 prescriptions from completed appointments

Prescriptions link to completed appointments. This INSERT selects from the appointments table itself, pulling real patient and doctor IDs so every foreign key is valid.

  • Paste the following to generate prescriptions:
-- Seed 5,000 prescriptions linked to completed appointments
INSERT INTO prescriptions (patient_id, doctor_id, appointment_id, medication, dosage, frequency, start_date, end_date)
SELECT
    a.patient_id,
    a.doctor_id,
    a.appointment_id,
    CASE (a.appointment_id % 5)
        WHEN 0 THEN 'Amoxicillin'
        WHEN 1 THEN 'Lisinopril'
        WHEN 2 THEN 'Metformin'
        WHEN 3 THEN 'Omeprazole'
        WHEN 4 THEN 'Atorvastatin'
    END,
    CASE (a.appointment_id % 3)
        WHEN 0 THEN '500mg'
        WHEN 1 THEN '10mg'
        WHEN 2 THEN '20mg'
    END,
    CASE (a.appointment_id % 3)
        WHEN 0 THEN 'Twice daily'
        WHEN 1 THEN 'Once daily'
        WHEN 2 THEN 'Three times daily'
    END,
    a.scheduled_start::DATE,
    a.scheduled_start::DATE + 30
FROM appointments a
WHERE a.status = 'completed'
ORDER BY a.appointment_id
LIMIT 5000;

Why select from the appointments table?

By selecting directly from appointments where status is 'completed', every prescription automatically has valid foreign keys. The patient_id, doctor_id, and appointment_id all reference rows that definitely exist.

  • Confirm the prescription count by running:
SELECT COUNT(*) FROM prescriptions;

You should see 5000.

How much data do you have now?

Your hospital database now contains 5 departments, 6 doctors, 500 patients, 50,000 appointments, and 5,000 prescriptions. That is enough data to expose real performance differences in the next step.

  • Add all three bulk INSERT statements to your seed.sql file in VS Code (below the existing department and doctor inserts) and save the file.
Write and run analytical queries

With real data in place, you can write the kind of multi-table JOIN queries a hospital admin would actually run. Each query combines data from 2-3 tables to answer a specific operational question.

  • Run this query to find all completed appointments for patients named 'Smith', showing doctor details:
-- Query 1: All completed appointments for patients named 'Smith' with doctor details
SELECT
    p.first_name || ' ' || p.last_name AS patient_name,
    d.first_name || ' ' || d.last_name AS doctor_name,
    d.specialization,
    a.scheduled_start,
    a.status
FROM appointments a
JOIN patients p ON p.patient_id = a.patient_id
JOIN doctors d ON d.doctor_id = a.doctor_id
WHERE p.last_name = 'Smith'
    AND a.status = 'completed'
ORDER BY a.scheduled_start DESC
LIMIT 20;

What does this query do?

  • It joins three tables: appointments as the center, patients for names, and doctors for specialization.
  • The || operator concatenates first and last names into a single column.
  • LIMIT 20 keeps the output readable. Without it, you would see hundreds of rows.

You should see up to 20 rows with patient names, doctor names, specializations, timestamps, and the status 'completed'.

  • Run this query to see appointment counts per department, broken down by status:
-- Query 2: Appointment count per department broken down by status
SELECT
    dep.name AS department,
    COUNT(*) AS total_appointments,
    COUNT(*) FILTER (WHERE a.status = 'completed') AS completed,
    COUNT(*) FILTER (WHERE a.status = 'cancelled') AS cancelled,
    COUNT(*) FILTER (WHERE a.status = 'no_show') AS no_shows
FROM appointments a
JOIN doctors d ON d.doctor_id = a.doctor_id
JOIN departments dep ON dep.department_id = d.department_id
GROUP BY dep.name
ORDER BY total_appointments DESC;

What is the FILTER clause?

COUNT(*) FILTER (WHERE ...) is PostgreSQL's way of doing conditional aggregation. Instead of writing multiple subqueries or CASE expressions, FILTER lets you count only rows matching a condition within a single GROUP BY.

You should see 5 rows (one per department) with columns showing total, completed, cancelled, and no-show counts.

  • Run this query to find the top 10 patients by prescription count:
-- Query 3: Top 10 patients by prescription count
SELECT
    p.first_name || ' ' || p.last_name AS patient_name,
    COUNT(pr.prescription_id) AS prescription_count,
    COUNT(DISTINCT pr.medication) AS unique_medications
FROM patients p
JOIN prescriptions pr ON pr.patient_id = p.patient_id
GROUP BY p.patient_id, p.first_name, p.last_name
ORDER BY prescription_count DESC
LIMIT 10;

Why GROUP BY p.patient_id too?

Two patients could share the same first and last name. Including p.patient_id in the GROUP BY ensures each row represents one unique patient, not an accidental merge of namesakes.

You should see 10 rows showing patient names, their prescription counts, and how many unique medications they have been prescribed.

  • Save all three queries to a file called queries.sql in VS Code. You will add a fourth query to this file in the next substep.
Run the slow query with timing enabled

Those queries returned results instantly because PostgreSQL is fast on small result sets. But what happens when a query has to scan the entire 50,000-row appointments table to filter by a date range AND a patient name?

  • Enable query timing in psql by running:
\timing on

What does \timing on do?

This is a psql meta-command that prints the wall-clock execution time after every query. It tells you exactly how many milliseconds PostgreSQL spent running your SQL.

Before you run the next query, think about this: the appointments table has 50,000 rows with no indexes beyond the primary key. The query needs to find rows matching a specific last name AND a date range. Do you think it will be fast or slow?

  • Run the date-range query filtering appointments for patients with last name 'Smith':
-- Query 4 (the slow one): appointments in a date range for patients named 'Smith'
SELECT
    p.first_name,
    p.last_name,
    d.first_name || ' ' || d.last_name AS doctor_name,
    a.scheduled_start,
    a.status
FROM appointments a
JOIN patients p ON p.patient_id = a.patient_id
JOIN doctors d ON d.doctor_id = a.doctor_id
WHERE p.last_name = 'Smith'
    AND a.scheduled_start BETWEEN '2024-06-01' AND '2024-12-31'
ORDER BY a.scheduled_start;

Look at the Time: line at the bottom of the output. On 50,000 rows without indexes, this query typically takes 50ms or more. That might sound fast, but in a production hospital with millions of rows and hundreds of concurrent users, this same pattern would time out.

Why is this query slow?

PostgreSQL has no way to jump directly to rows matching 'Smith' or rows in the date range. It must read every single row in the patients table and every single row in the appointments table to find matches. This is called a sequential scan.

In the next step, you will use EXPLAIN ANALYZE to see exactly where the time goes, then fix it with targeted indexes.

  • Add Query 4 to your queries.sql file in VS Code and save.

Query returning zero rows?

  • Check that your date range matches the data. The seed uses timestamps starting from 2024-01-01 across 730 days. If you changed the seed dates, adjust the BETWEEN clause to match.
  • Confirm you have Smith patients by running SELECT COUNT(*) FROM patients WHERE last_name = 'Smith'; and checking you get approximately 100.

Help me troubleshoot why my date-range query returns no results.

✔️ Awesome, I've got everything!

Great. Confirm you have saved both seed.sql (with all bulk inserts) and queries.sql (with all four queries) in VS Code.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file. Scroll up and confirm each of the four queries is present in order.

Your file should contain these four queries in this order:

  • Query 1: Completed appointments for 'Smith' patients with doctor details (uses JOIN on appointments, patients, doctors with WHERE filter on last_name and status).
  • Query 2: Appointment count per department by status (uses COUNT(*) FILTER with GROUP BY dep.name).
  • Query 3: Top 10 patients by prescription count (uses COUNT and COUNT(DISTINCT) with GROUP BY patient).
  • Query 4: Date-range query for 'Smith' appointments (the slow one, uses BETWEEN on scheduled_start).

For seed.sql, confirm it contains the department and doctor inserts from earlier, plus the three bulk INSERT...SELECT statements (patients, appointments, prescriptions) you added in this step.

Your database is loaded and you have seen the problem firsthand. A query that should be instant is taking tens of milliseconds because PostgreSQL scans every row. Next up, you will use EXPLAIN ANALYZE to see exactly which scans are costing you time, then add targeted indexes to fix them.

Diagnose and Optimize with EXPLAIN ANALYZE

You saw the slow query take a noticeable amount of time in the last step. On 50,000 rows that's merely annoying. In a production hospital with millions of appointments, it would time out entirely.

The problem is that PostgreSQL scanned every single row in every table to find matches. EXPLAIN ANALYZE is PostgreSQL's built-in profiler. It runs your query and reports exactly how much time each operation took, so you can see where the bottleneck lives and fix it with targeted indexes.

In this step, get ready to:
  • Run EXPLAIN ANALYZE on the slow query and identify sequential scans.
  • Add targeted indexes to eliminate full table scans.
  • Re-run EXPLAIN ANALYZE and confirm the performance improvement.
Run EXPLAIN ANALYZE on the slow query

EXPLAIN ANALYZE prepends any query and tells PostgreSQL to actually execute it while recording timing for every operation. The output is a tree of nodes. Each node represents one operation the database performed.

  • In your psql session connected to the hospital database, run EXPLAIN ANALYZE on the slow query from the last step:
EXPLAIN ANALYZE
SELECT
    p.first_name,
    p.last_name,
    d.first_name || ' ' || d.last_name AS doctor_name,
    a.scheduled_start,
    a.status
FROM appointments a
JOIN patients p ON p.patient_id = a.patient_id
JOIN doctors d ON d.doctor_id = a.doctor_id
WHERE p.last_name = 'Smith'
    AND a.scheduled_start BETWEEN '2024-06-01' AND '2024-12-31'
ORDER BY a.scheduled_start;

What does this output show?

The output is a tree of operations PostgreSQL performed to answer your query. Look for these key pieces:

  • Seq Scan means PostgreSQL read every row in that table. This is the expensive part you want to eliminate.
  • The actual time numbers tell you how many milliseconds each node took.
  • Filter lines show rows that were read but discarded because they did not match your WHERE clause.

You should see Seq Scan on both the appointments and patients tables. PostgreSQL read all 50,000 appointment rows and all 500 patient rows just to find the handful that match your filters.

Output looks confusing?

The plan can be long. Focus on finding lines that say Seq Scan on appointments and Seq Scan on patients. Note the actual time numbers next to them. Those are your bottlenecks.

help me read my EXPLAIN ANALYZE output

Add targeted indexes based on the EXPLAIN output

An index is a separate data structure that lets PostgreSQL jump directly to matching rows instead of scanning an entire table. Think of it like the index at the back of a textbook. You look up the term and it tells you the exact page number.

Your EXPLAIN ANALYZE output showed sequential scans on the WHERE and JOIN columns. Those are the exact columns that need indexes.

  • Create the four indexes that target the slow query's filters and joins by running this in psql:
-- For WHERE p.last_name = 'Smith'
CREATE INDEX idx_patients_last_name ON patients (last_name);

-- For WHERE a.scheduled_start BETWEEN ...
CREATE INDEX idx_appointments_scheduled_start ON appointments (scheduled_start);

-- For JOIN on appointments.patient_id
CREATE INDEX idx_appointments_patient_id ON appointments (patient_id);

-- For JOIN on appointments.doctor_id
CREATE INDEX idx_appointments_doctor_id ON appointments (doctor_id);

What does each index target?

  • idx_patients_last_name eliminates the Seq Scan on patients for the WHERE p.last_name = 'Smith' filter.
  • idx_appointments_scheduled_start lets PostgreSQL jump to the date range instead of scanning all 50,000 rows.
  • idx_appointments_patient_id and idx_appointments_doctor_id speed up the JOIN lookups between appointments, patients, and doctors.

You should see CREATE INDEX printed four times, one for each index created.

Seeing "relation already exists"?

This means an index with that name already exists. You can drop it first with DROP INDEX idx_name; and then re-create it. Or if you already ran the commands successfully before, the indexes are already in place and you can move on.

help me fix my CREATE INDEX error

Re-run EXPLAIN ANALYZE and compare

Before you run this again, predict: do you think the output will still show Seq Scan on the appointments table, or something different?

  • Run the same EXPLAIN ANALYZE query again:
EXPLAIN ANALYZE
SELECT
    p.first_name,
    p.last_name,
    d.first_name || ' ' || d.last_name AS doctor_name,
    a.scheduled_start,
    a.status
FROM appointments a
JOIN patients p ON p.patient_id = a.patient_id
JOIN doctors d ON d.doctor_id = a.doctor_id
WHERE p.last_name = 'Smith'
    AND a.scheduled_start BETWEEN '2024-06-01' AND '2024-12-31'
ORDER BY a.scheduled_start;

The output now shows Index Scan or Bitmap Index Scan where you previously saw Seq Scan on the appointments table. The execution time has dropped significantly.

Why the trade-off matters

Indexes speed up reads but add overhead to writes. Every INSERT and UPDATE must also update each affected index. For a hospital system where reads far outnumber writes, this trade-off is worth it.

In interviews, naming this trade-off shows you understand that indexes are not free. You choose them deliberately based on query patterns.

  • Now save these indexes to a file. Switch to VS Code and create a new file called indexes.sql in the same folder as your other .sql files.
  • Paste the following into indexes.sql:
-- Indexes added after EXPLAIN ANALYZE diagnosis

-- For WHERE p.last_name = 'Smith'
CREATE INDEX idx_patients_last_name ON patients (last_name);

-- For WHERE a.scheduled_start BETWEEN ...
CREATE INDEX idx_appointments_scheduled_start ON appointments (scheduled_start);

-- For JOIN on appointments.patient_id
CREATE INDEX idx_appointments_patient_id ON appointments (patient_id);

-- For JOIN on appointments.doctor_id
CREATE INDEX idx_appointments_doctor_id ON appointments (doctor_id);
  • Save indexes.sql.
  • Now open schema.sql and add the index definitions at the very end of the file, after the prescriptions table:
-- Performance indexes (added after EXPLAIN ANALYZE diagnosis)
CREATE INDEX idx_patients_last_name ON patients (last_name);
CREATE INDEX idx_appointments_scheduled_start ON appointments (scheduled_start);
CREATE INDEX idx_appointments_patient_id ON appointments (patient_id);
CREATE INDEX idx_appointments_doctor_id ON appointments (doctor_id);
  • Save schema.sql.

Why save indexes in both files?

indexes.sql documents the indexes as a standalone migration you can run on an existing database. schema.sql represents the complete schema for a fresh database. Keeping both in sync means anyone can set up the database from scratch or apply indexes to an existing one.

✔️ Awesome, I've got everything!

Great. Double check you have saved both indexes.sql and schema.sql.

ⓧ I'd like to double check the full code

Here is the complete indexes.sql file:

-- Indexes added after EXPLAIN ANALYZE diagnosis

-- For WHERE p.last_name = 'Smith'
CREATE INDEX idx_patients_last_name ON patients (last_name);

-- For WHERE a.scheduled_start BETWEEN ...
CREATE INDEX idx_appointments_scheduled_start ON appointments (scheduled_start);

-- For JOIN on appointments.patient_id
CREATE INDEX idx_appointments_patient_id ON appointments (patient_id);

-- For JOIN on appointments.doctor_id
CREATE INDEX idx_appointments_doctor_id ON appointments (doctor_id);

And the lines you added to the end of schema.sql:

-- Performance indexes (added after EXPLAIN ANALYZE diagnosis)
CREATE INDEX idx_patients_last_name ON patients (last_name);
CREATE INDEX idx_appointments_scheduled_start ON appointments (scheduled_start);
CREATE INDEX idx_appointments_patient_id ON appointments (patient_id);
CREATE INDEX idx_appointments_doctor_id ON appointments (doctor_id);

EXPLAIN ANALYZE still shows Seq Scan?

PostgreSQL's planner sometimes chooses a sequential scan when it estimates the index would not help (e.g., if the table is very small or the filter matches most rows). Confirm your indexes exist by running \di in psql. You should see all four index names listed.

If the indexes exist but the planner still picks Seq Scan, try running ANALYZE appointments; to refresh table statistics. Then re-run your EXPLAIN ANALYZE query.

help me understand why PostgreSQL is still using Seq Scan after I added indexes

Your database is now optimized for the queries your hospital actually runs. Next up, you will tackle a real scheduling problem: preventing doctors from being double-booked.

Secret mission

Prevent Double-Booking with EXCLUDE Constraints

Your indexes make queries fast, but they do not enforce business logic. A real hospital must guarantee that no doctor is double-booked. Can you use PostgreSQL's EXCLUDE constraint to make overlapping appointments physically impossible at the database level?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally on your PostgreSQL instance, so there are no ongoing costs.

Resources you used:

  • A PostgreSQL database named hospital on your local machine.
  • SQL files: schema.sql, seed.sql, queries.sql, indexes.sql, and secret_mission.sql.

Keep everything running

No action needed. Choose this if you are still actively experimenting with your hospital schema or preparing for interviews.

  • The hospital database stays on your local PostgreSQL instance at no cost.
  • Your SQL files remain wherever you saved them for future reference.
  • PostgreSQL continues running as a background service, using minimal system resources.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory while keeping your database files intact. You can restart the service later and pick up where you left off.

  • Exit psql if you are still connected by typing \q.
  • Stop the PostgreSQL service by running:
sudo systemctl stop postgresql
  • When you want to resume, start the service again with:
sudo systemctl start postgresql
  • Your hospital database and all its data will still be there when you reconnect.

Delete - I don't want to use this again

Remove the hospital database and all associated SQL files. This is irreversible.

  • Exit psql if you are still connected by typing \q.
  • Drop the hospital database by running:
sudo -u postgres dropdb hospital

Alternative if your Linux user has PostgreSQL access

If you created the database without sudo -u postgres earlier, you can simply run dropdb hospital instead.

  • Delete your SQL files by running:
rm schema.sql seed.sql queries.sql indexes.sql secret_mission.sql
  • Confirm everything is removed by running:
ls *.sql
  • You should see a message indicating no matching files were found.

Nice Work!

Nice Work!

Nice work! You just designed, built, and optimized a production-quality hospital management database entirely in PostgreSQL.

You've learned how to:

  • Design a normalized (3NF) relational schema from business requirements, enforcing data integrity with CHECK, UNIQUE, NOT NULL, and FOREIGN KEY constraints so invalid data is rejected at the database level.
  • Generate 50,000+ rows of realistic test data with generate_series and write multi-table JOIN queries with filtering and aggregation.
  • Diagnose a slow query with EXPLAIN ANALYZE and fix it with targeted indexes, replacing sequential scans with index scans.
  • Secret Mission: Used EXCLUDE constraints with btree_gist to prevent appointment double-booking at the database level.

Ready to quiz yourself?