Hospital Management Database Schema

Tables in this schema

Schema (SQL)

MySQL-dialect DDL. Open it in the editor for a visual ER diagram you can edit, or copy the SQL straight into your project.

CREATE TABLE departments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  location VARCHAR(120)
);

CREATE TABLE doctors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  department_id INT NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  specialty VARCHAR(100),
  email VARCHAR(254) UNIQUE,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE patients (
  id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(120) NOT NULL,
  date_of_birth DATE,
  phone VARCHAR(32),
  created_at DATETIME NOT NULL
);

CREATE TABLE appointments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  patient_id INT NOT NULL,
  doctor_id INT NOT NULL,
  scheduled_at DATETIME NOT NULL,
  status VARCHAR(20) NOT NULL,
  reason VARCHAR(255),
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id)
);

CREATE TABLE prescriptions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id INT NOT NULL,
  medication VARCHAR(200) NOT NULL,
  dosage VARCHAR(100),
  instructions VARCHAR(500),
  FOREIGN KEY (appointment_id) REFERENCES appointments(id)
);