Crowsfoot

Clinic appointment database schema: ER diagram and SQL

An appointment system connects people who need care with the people who give it, at a particular time. This sketch covers the essentials and shows how a record can hang off an appointment.

Open this schema in the editor Jump to the SQL
doctors PK id bigint name varchar specialty varchar patients PK id bigint name varchar date_of_birth date phone varchar appointments PK id bigint FK patient_id bigint FK doctor_id bigint starts_at timestamp status varchar reason text prescriptions PK id bigint FK appointment_id bigint medication varchar dosage varchar

Tables in this schema

doctors

Clinicians who see patients.

patients

People receiving care.

appointments

A scheduled visit between one patient and one doctor.

prescriptions

Medication prescribed during an appointment. One visit can produce several.

How the tables relate

  • appointments.patient_id references patients.id: one row in patients can have many rows in appointments.
  • appointments.doctor_id references doctors.id: one row in doctors can have many rows in appointments.
  • prescriptions.appointment_id references appointments.id: one row in appointments can have many rows in prescriptions.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE doctors (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  specialty varchar(80) NOT NULL
);

CREATE TABLE patients (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  date_of_birth date NOT NULL,
  phone varchar(30)
);

CREATE TABLE appointments (
  id bigint PRIMARY KEY,
  patient_id bigint NOT NULL REFERENCES patients (id),
  doctor_id bigint NOT NULL REFERENCES doctors (id),
  starts_at timestamptz NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'scheduled',
  reason text
);

CREATE TABLE prescriptions (
  id bigint PRIMARY KEY,
  appointment_id bigint NOT NULL REFERENCES appointments (id),
  medication varchar(160) NOT NULL,
  dosage varchar(80) NOT NULL
);

Design notes

  • Health data is sensitive and regulated in many places. Treat this as a structural sketch, not a compliance-ready design.
  • Add an index on (doctor_id, starts_at) so “what is Dr. Lee’s day?” stays fast.
  • Keeping status as a short text value is simple. A small statuses table or enum works too once the list is fixed.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.