Hospital Database Project in MySQL

The project

A hospital database is a great DBMS project. It models patients, doctors, and the appointments between them. Since a patient can see many doctors and a doctor sees many patients, the appointments table resolves this many-to-many relationship.

Designing the tables

• patients — id, name, phone, age.
• doctors — id, name, specialty.
• appointments — id, patient_id (FK), doctor_id (FK), appointment_time, notes.

Patients and doctors connect through appointments, each recording one visit.

Creating the schema

CREATE TABLE patients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(15), age INT
);
CREATE TABLE doctors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    specialty VARCHAR(40)
);
CREATE TABLE appointments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT, doctor_id INT,
    appointment_time DATETIME, notes VARCHAR(200),
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
);

The appointments link

The appointments table is the many-to-many link. Each row is one visit: which patient saw which doctor, when, and any notes. This keeps patient and doctor details in their own tables, without repetition.

Sample data

INSERT INTO patients (name, phone, age) VALUES ('Aman', '9990001', 30), ('Diya', '9990002', 25);
INSERT INTO doctors (name, specialty) VALUES ('Dr. Rao', 'Cardiology'), ('Dr. Iyer', 'Dermatology');
INSERT INTO appointments (patient_id, doctor_id, appointment_time, notes) VALUES
    (1, 1, '2026-09-22 10:00:00', 'Chest pain'),
    (2, 2, '2026-09-22 11:30:00', 'Skin rash');

Useful queries

-- a doctor's appointments today
SELECT p.name AS patient, a.appointment_time
FROM appointments a
JOIN patients p ON a.patient_id = p.id
WHERE a.doctor_id = 1 AND DATE(a.appointment_time) = CURDATE();

-- all appointments with patient and doctor names
SELECT p.name AS patient, d.name AS doctor, a.appointment_time
FROM appointments a
JOIN patients p ON a.patient_id = p.id
JOIN doctors d ON a.doctor_id = d.id;

-- appointments per doctor
SELECT d.name, COUNT(*) AS total
FROM appointments a JOIN doctors d ON a.doctor_id = d.id
GROUP BY d.id;

Common mistakes

• Putting doctor_id directly in patients. That cannot model many-to-many; use appointments.
• Storing time as text. Use DATETIME so date functions work.
• No foreign keys. Link appointments to real patients and doctors.
• Repeating doctor details per appointment. Keep them in the doctors table.

Summary

• A hospital database has patients, doctors and an appointments link table.
• patients ↔ doctors is many-to-many, resolved by appointments.
• Each appointment records patient, doctor, time and notes.
• Use DATETIME for appointment times.
• Query schedules and counts with JOIN and GROUP BY.

Next, revise many-to-many in the library database project, or the ER model.