Why should you care?

OPD queue and billing must link same patient_id — wrong JOIN sends lab results to wrong person — life safety not just UX.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE patients (
  patient_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  phone VARCHAR(15) NOT NULL,
  dob DATE NOT NULL
);
CREATE TABLE appointments (
  appt_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  doctor_id INT UNSIGNED NOT NULL,
  appt_at DATETIME NOT NULL,
  status ENUM('BOOKED','DONE','CANCELLED') DEFAULT 'BOOKED',
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);
SELECT p.full_name, a.appt_at, a.status
FROM appointments a
JOIN patients p ON p.patient_id = a.patient_id
WHERE DATE(a.appt_at) = CURDATE() AND a.status = 'BOOKED';

What happened?

• Today’s booked appointments with patient names for reception desk.
• FK ensures appointment always has valid patient.
• Audit triggers (earlier lesson) on status changes.

Practice next

1. Create patients and appointments in DataFlow health module.
2. Insert sample today appointments.
3. Run reception desk SELECT.
4. Add bills table JOIN appointment for daily revenue.
5. Mask phone in SELECT for reception role view.

Remember

Patient-centric FK graph. Appointments filtered by date and status. Audit and least privilege for PHI.

DataFlow clinic demo

Small chain uses MySQL for OPD schedule; encrypts backups at rest.

Outcome: Desk screen shows correct queue order.