How to Design a Database Schema for a Healthcare Appointment System

Introduction

A healthcare appointment system connects patients with healthcare providers, enabling scheduling, medical record management, and billing. Designing the database schema requires careful consideration of patient privacy, appointment logistics, provider availability, and medical history tracking.

In this post, we will walk through the key entities, relationships, and tables needed to build a robust healthcare appointment system.

Entities and Relationships

The core entities in a healthcare appointment system include:

• Patients — individuals seeking medical care
• Providers — doctors, specialists, nurses, and other healthcare professionals
• Clinics/Facilities — physical locations where appointments take place
• Appointments — scheduled meetings between patients and providers
• Medical Records — patient health history and visit notes
• Prescriptions — medications prescribed during or after visits
• Insurance Plans — patient insurance coverage details
• Billing/Invoices — charges for services rendered

Key Relationships

• A patient can book many appointments with different providers
• A provider works at one or more clinics and has defined availability slots
• Each appointment generates medical records and may result in prescriptions
• Billing is tied to appointments and may reference insurance plans

ER Diagram

Scroll or pinch to zoom · drag to pan · double-click to zoom in−+Reset

Entity-relationship diagram — healthcare appointment system

Key Tables

patients

providers

clinics

provider_clinics

availability_slots

appointments

medical_records

prescriptions

insurance_plans

invoices

Example Queries

Find all upcoming appointments for a patient:

SELECT
  a.scheduled_at,
  pr.first_name AS doctor_first,
  pr.last_name  AS doctor_last,
  pr.specialization,
  c.name        AS clinic
FROM appointments a
JOIN providers pr ON a.provider_id = pr.id
JOIN clinics c    ON a.clinic_id   = c.id
JOIN patients p   ON a.patient_id  = p.id
WHERE a.patient_id = :patient_id
  AND a.scheduled_at > NOW()
  AND a.status IN ('scheduled', 'confirmed')
ORDER BY a.scheduled_at;

Get a provider's available slots for a given day:

SELECT
  s.start_time,
  s.end_time,
  s.slot_duration_minutes,
  c.name AS clinic
FROM availability_slots s
JOIN clinics c ON s.clinic_id = c.id
WHERE s.provider_id = :provider_id
  AND s.day_of_week = EXTRACT(DOW FROM :target_date::date)
  AND s.is_active = true;

Get full medical history for a patient:

SELECT
  mr.diagnosis,
  mr.symptoms,
  mr.treatment_notes,
  mr.vitals,
  a.scheduled_at,
  pr.first_name || ' ' || pr.last_name AS provider
FROM medical_records mr
JOIN appointments a ON mr.appointment_id = a.id
JOIN providers pr   ON mr.provider_id    = pr.id
WHERE mr.patient_id = :patient_id
ORDER BY a.scheduled_at DESC;

Calculate outstanding balance for a patient:

SELECT SUM(patient_due) AS total_outstanding
FROM invoices
WHERE patient_id = :patient_id
  AND status IN ('pending', 'overdue');

Extensibility

This schema can be extended in several ways:

• Telemedicine — add a meeting_url and appointment_type (in-person vs virtual) to the appointments table
• Lab Results — create a lab_tests table linked to medical records for blood work, imaging, etc.
• Referrals — track provider-to-provider referrals with a referrals table
• Reviews/Ratings — let patients rate providers after appointments
• Notifications — store appointment reminders and notification preferences
• Waitlist — manage cancellation slots with a waitlist queue per provider
• Multi-language Support — add locale preferences to patients for communication