-- ═══════════════════════════════════════════════════════════
-- MEDISYNC DATABASE SCHEMA | MySQL 8.0
-- ═══════════════════════════════════════════════════════════
CREATE DATABASE medisync_db
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE medisync_db;
-- Users (unified auth table with role discriminator)
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(180) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL, -- bcrypt hashed
role ENUM('patient','doctor','admin') DEFAULT 'patient',
is_active TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Doctors (extended profile linked to users)
CREATE TABLE doctors (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT UNIQUE,
specialization VARCHAR(120),
qualifications TEXT,
reg_number VARCHAR(60),
consultation_fee DECIMAL(10,2),
bio TEXT,
photo_url VARCHAR(255),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Patients (medical profile)
CREATE TABLE patients (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT UNIQUE,
dob DATE,
gender ENUM('male','female','other'),
nic VARCHAR(20),
phone VARCHAR(20),
notes TEXT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Availability slots per doctor
CREATE TABLE availability (
id INT AUTO_INCREMENT PRIMARY KEY,
doctor_id INT,
day_of_week TINYINT, -- 0=Mon … 6=Sun
start_time TIME,
end_time TIME,
is_active TINYINT(1) DEFAULT 1,
FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE
);
-- Appointments (core booking table)
CREATE TABLE appointments (
id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT,
doctor_id INT,
date DATE NOT NULL,
time_slot TIME NOT NULL,
status ENUM('pending','confirmed','completed','cancelled') DEFAULT 'pending',
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (patient_id) REFERENCES patients(id),
FOREIGN KEY (doctor_id) REFERENCES doctors(id),
UNIQUE KEY uq_slot (doctor_id, date, time_slot)
);
-- Payments
CREATE TABLE payments (
id INT AUTO_INCREMENT PRIMARY KEY,
appointment_id INT UNIQUE,
amount DECIMAL(10,2) NOT NULL,
method ENUM('card','bank','cash'),
status ENUM('pending','paid','refunded') DEFAULT 'pending',
transaction_ref VARCHAR(120),
paid_at TIMESTAMP,
FOREIGN KEY (appointment_id) REFERENCES appointments(id)
);
-- Notifications log
CREATE TABLE notifications (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
type ENUM('sms','email','push'),
message TEXT,
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_read TINYINT(1) DEFAULT 0,
FOREIGN KEY (user_id) REFERENCES users(id)
);
