MySQL Appointment System Project: Doctors, Patients

Project goal: a booking system where a doctor can never have two appointments at the same time. This is the classic “overlapping ranges” problem. A simple UNIQUE index cannot express it, so you will use a trigger to enforce the rule inside the database.

Requirements

Doctors have a name and a specialty. Patients have a name and a phone.
An appointment is for one patient and one doctor, with a start and an end time, and a status (booked, done, cancelled).
A doctor cannot have two non-cancelled appointments that overlap. The end must be after the start.
Reports: a doctor’s agenda for a day, free time slots, patients’ history, cancellations per doctor.

Tables and relationships

Table	Relationship
`doctors` 1 — N `appointments`	Each appointment has one doctor
`patients` 1 — N `appointments`	Each appointment has one patient

The schema

query.sql

SQL

CREATE TABLE doctors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    specialty VARCHAR(40) NOT NULL
);

CREATE TABLE patients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    UNIQUE KEY uq_patients_phone (phone)
);

CREATE TABLE appointments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    doctor_id INT UNSIGNED NOT NULL,
    patient_id INT UNSIGNED NOT NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    status ENUM('booked', 'done', 'cancelled') NOT NULL DEFAULT 'booked',
    CONSTRAINT chk_appointment_times CHECK (ends_at > starts_at),
    CONSTRAINT fk_appt_doctor FOREIGN KEY (doctor_id) REFERENCES doctors (id),
    CONSTRAINT fk_appt_patient FOREIGN KEY (patient_id) REFERENCES patients (id),
    KEY idx_appt_doctor_time (doctor_id, starts_at, ends_at),
    KEY idx_appt_patient (patient_id, starts_at)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

The index (doctor_id, starts_at, ends_at) is the one the overlap check uses.

Preventing overlaps with a trigger

Two time ranges overlap when each starts before the other ends. A BEFORE INSERT trigger can look for such an existing appointment for the same doctor, and refuse the new one with SIGNAL (Triggers):

query.sql

SQL

DELIMITER //

CREATE TRIGGER trg_appt_no_overlap_insert BEFORE INSERT ON appointments FOR EACH ROW
BEGIN
    IF NEW.status <> 'cancelled' AND EXISTS (
        SELECT 1 FROM appointments a
        WHERE a.doctor_id = NEW.doctor_id AND a.status <> 'cancelled'
          AND a.starts_at < NEW.ends_at AND NEW.starts_at < a.ends_at
    ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The doctor already has an appointment at that time';
    END IF;
END//

CREATE TRIGGER trg_appt_no_overlap_update BEFORE UPDATE ON appointments FOR EACH ROW
BEGIN
    IF NEW.status <> 'cancelled' AND EXISTS (
        SELECT 1 FROM appointments a
        WHERE a.doctor_id = NEW.doctor_id AND a.status <> 'cancelled' AND a.id <> NEW.id
          AND a.starts_at < NEW.ends_at AND NEW.starts_at < a.ends_at
    ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The doctor already has an appointment at that time';
    END IF;
END//

DELIMITER ;

Output

OUTPUT

1
2
3

Query OK, 0 rows affected

Query OK, 0 rows affected

The two conditions a.starts_at < NEW.ends_at AND NEW.starts_at < a.ends_at are the general overlap test. An appointment that ends at 10:30 and another that starts at 10:30 do not overlap (each side uses strict <).

A warning about concurrency: the trigger checks and inserts in separate steps. Two sessions inserting overlapping appointments at the same instant could both pass the check. For a real system, lock the doctor’s row first (SELECT ... FROM doctors WHERE id = ? FOR UPDATE) in the same transaction as the insert, so that bookings for one doctor happen one at a time (Locking).

Sample data

query.sql

SQL

INSERT INTO doctors (name, specialty) VALUES ('Dr. Rao', 'General'), ('Dr. Mehta', 'Pediatrics');
INSERT INTO patients (name, phone) VALUES ('Maya', '9000000001'), ('Rohan', '9000000002'), ('Isha', '9000000003'), ('Kabir', '9000000004');

INSERT INTO appointments (doctor_id, patient_id, starts_at, ends_at, status) VALUES
(1, 1, '2026-03-10 09:00:00', '2026-03-10 09:30:00', 'done'),
(1, 2, '2026-03-10 09:30:00', '2026-03-10 10:00:00', 'done'),
(1, 3, '2026-03-10 11:00:00', '2026-03-10 11:45:00', 'booked'),
(2, 4, '2026-03-10 09:00:00', '2026-03-10 09:30:00', 'booked'),
(1, 4, '2026-03-10 10:00:00', '2026-03-10 10:30:00', 'cancelled');

Output

OUTPUT

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

The last appointment is cancelled, so it does not block that slot. The first two touch at 9:30 and are accepted. Now the conflicts:

query.sql

SQL

INSERT INTO appointments (doctor_id, patient_id, starts_at, ends_at) VALUES (1, 2, '2026-03-10 09:45:00', '2026-03-10 10:15:00');

Output

OUTPUT

ERROR 1644 (45000): The doctor already has an appointment at that time

Dr. Rao is busy between 9:30 and 10:00, so 9:45 to 10:15 overlaps. The rule is enforced by the database, whatever application inserts the row. A different doctor, or the cancelled slot, is allowed:

query.sql

SQL

1
2

INSERT INTO appointments (doctor_id, patient_id, starts_at, ends_at) VALUES (2, 1, '2026-03-10 09:15:00', '2026-03-10 09:45:00');
INSERT INTO appointments (doctor_id, patient_id, starts_at, ends_at) VALUES (1, 4, '2026-03-10 10:00:00', '2026-03-10 10:30:00');

Output

OUTPUT

ERROR 1644 (45000): The doctor already has an appointment at that time

(The first statement fails too: Dr. Mehta has an appointment from 9:00 to 9:30, and 9:15 to 9:45 overlaps it. The second works, because the earlier appointment in that slot was cancelled.)

Query 1: a doctor’s agenda

query.sql

SQL

SELECT TIME(a.starts_at) AS starts, TIME(a.ends_at) AS ends, p.name AS patient, a.status
FROM appointments a JOIN patients p ON p.id = a.patient_id
WHERE a.doctor_id = 1 AND a.starts_at >= '2026-03-10' AND a.starts_at < '2026-03-11' AND a.status <> 'cancelled'
ORDER BY a.starts_at;

Output

OUTPUT

+----------+----------+---------+--------+
| starts   | ends     | patient | status |
+----------+----------+---------+--------+
| 09:00:00 | 09:30:00 | Maya    | done   |
| 09:30:00 | 10:00:00 | Rohan   | done   |
| 10:00:00 | 10:30:00 | Kabir   | booked |
| 11:00:00 | 11:45:00 | Isha    | booked |
+----------+----------+---------+--------+
4 rows in set

The date range >= '2026-03-10' AND < '2026-03-11' can use the index. DATE(starts_at) = '2026-03-10' cannot (Date Filters).

Query 2: free time between appointments

The gaps in a doctor’s day, from the end of one appointment to the start of the next:

query.sql

SQL

SELECT ends_at AS free_from, next_start AS free_until, TIMESTAMPDIFF(MINUTE, ends_at, next_start) AS minutes_free
FROM (
    SELECT a.ends_at, LEAD(a.starts_at) OVER (ORDER BY a.starts_at) AS next_start
    FROM appointments a
    WHERE a.doctor_id = 1 AND a.status <> 'cancelled' AND a.starts_at >= '2026-03-10' AND a.starts_at < '2026-03-11'
) gaps
WHERE next_start IS NOT NULL AND next_start > ends_at
ORDER BY free_from;

Output

OUTPUT

+---------------------+---------------------+--------------+
| free_from           | free_until          | minutes_free |
+---------------------+---------------------+--------------+
| 2026-03-10 10:30:00 | 2026-03-10 11:00:00 |           30 |
+---------------------+---------------------+--------------+
1 row in set

LEAD looks at the next appointment’s start. The window function works on the ordered list of that day’s appointments (LAG and LEAD). The time before the first appointment and after the last depends on the opening hours, which you would keep in a working_hours table.

Query 3: a patient’s history and cancellations per doctor

query.sql

SQL

SELECT a.starts_at, d.name AS doctor, a.status FROM appointments a JOIN doctors d ON d.id = a.doctor_id WHERE a.patient_id = 4 ORDER BY a.starts_at;

SELECT d.name, COUNT(*) AS appointments, SUM(a.status = 'cancelled') AS cancelled, ROUND(SUM(a.status = 'cancelled') / COUNT(*) * 100) AS cancelled_pct
FROM doctors d JOIN appointments a ON a.doctor_id = d.id
GROUP BY d.id, d.name ORDER BY d.name;

Output

OUTPUT

+---------------------+-----------+-----------+
| starts_at           | doctor    | status    |
+---------------------+-----------+-----------+
| 2026-03-10 09:00:00 | Dr. Mehta | booked    |
| 2026-03-10 10:00:00 | Dr. Rao   | booked    |
| 2026-03-10 10:00:00 | Dr. Rao   | cancelled |
+---------------------+-----------+-----------+
3 rows in set

+-----------+--------------+-----------+---------------+
| name      | appointments | cancelled | cancelled_pct |
+-----------+--------------+-----------+---------------+
| Dr. Mehta |            1 |         0 |             0 |
| Dr. Rao   |            5 |         1 |            20 |
+-----------+--------------+-----------+---------------+
2 rows in set

Common mistakes

Using BETWEEN for overlap tests, or <= on both sides. Then an appointment ending at 9:30 conflicts with one starting at 9:30. Use strict <.
Checking overlaps in the application only. Another app, an import script or a bug skips the check. Put the rule in the database.
Ignoring concurrency. Two simultaneous requests can both pass the check. Lock the doctor row.
Deleting cancelled rows. You lose the history that reports need. Keep the row and change the status.
Storing the date and the time in two columns and then struggling with appointments that cross midnight. One DATETIME for the start and one for the end is simpler.
Mixing time zones. Store UTC, or one fixed clinic time zone, and convert for display.

Final result

query.sql

SQL

SELECT status, COUNT(*) AS appointments FROM appointments GROUP BY status ORDER BY status;

Output

OUTPUT

+-----------+--------------+
| status    | appointments |
+-----------+--------------+
| booked    |            3 |
| done      |            2 |
| cancelled |            1 |
+-----------+--------------+
3 rows in set

The database now refuses double bookings by itself. Next steps: opening hours and holidays tables, a slots table if the clinic books fixed-length slots (then UNIQUE (doctor_id, slot_start) replaces the trigger), reminders sent by a scheduled job, and an index-backed “next free slot” query.