-- Patients table to store patient information
CREATE TABLE patients (
patient_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
gender VARCHAR(10) CHECK (gender IN ('Male', 'Female', 'Other')),
blood_type VARCHAR(5) CHECK (blood_type IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-')),
address VARCHAR(200),
phone VARCHAR(20),
email VARCHAR(100),
insurance_provider VARCHAR(100),
insurance_policy_number VARCHAR(50),
admission_date DATE,
discharge_date DATE,
emergency_contact_name VARCHAR(100),
emergency_contact_phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Staff table for hospital employees (doctors, nurses, etc.)
CREATE TABLE staff (
staff_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
role VARCHAR(50) NOT NULL CHECK (role IN ('Doctor', 'Nurse', 'Technician', 'Administrative', 'Support')),
department VARCHAR(50) NOT NULL,
specialization VARCHAR(100),
license_number VARCHAR(50) UNIQUE,
hire_date DATE NOT NULL,
shift_preference VARCHAR(20) CHECK (shift_preference IN ('Morning', 'Afternoon', 'Night', 'Rotating')),
salary NUMERIC(10, 2),
phone VARCHAR(20),
email VARCHAR(100),
supervisor_id INTEGER REFERENCES staff(staff_id),
status VARCHAR(20) DEFAULT 'Active' CHECK (status IN ('Active', 'On Leave', 'Terminated')),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Medical records for patient treatments
CREATE TABLE medical_records (
record_id SERIAL PRIMARY KEY,
patient_id INTEGER NOT NULL REFERENCES patients(patient_id),
attending_staff_id INTEGER NOT NULL REFERENCES staff(staff_id),
diagnosis VARCHAR(200) NOT NULL,
treatment_plan TEXT,
medication TEXT,
admission_date DATE NOT NULL,
discharge_date DATE,
room_number VARCHAR(10),
treatment_cost NUMERIC(10, 2),
insurance_covered NUMERIC(10, 2),
patient_paid NUMERIC(10, 2),
follow_up_date DATE,
notes TEXT,
record_status VARCHAR(20) DEFAULT 'Active' CHECK (record_status IN ('Active', 'Completed', 'Cancelled')),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Adding indexes for performance optimization
CREATE INDEX idx_patient_name ON patients(last_name, first_name);
CREATE INDEX idx_staff_role_dept ON staff(role, department);
CREATE INDEX idx_medical_records_patient ON medical_records(patient_id);
CREATE INDEX idx_medical_records_staff ON medical_records(attending_staff_id);
CREATE INDEX idx_medical_records_dates ON medical_records(admission_date, discharge_date);