Skip to content

schema-design.md

Latest commit

 

History

History
109 lines (90 loc) · 3.24 KB

schema-design.md

File metadata and controls

109 lines (90 loc) · 3.24 KB

Smart Clinic Management System Database Design

MySQL Database Design

Table: patients

  • id: INT, Primary Key, Auto Increment
  • first_name: VARCHAR(50), NOT NULL
  • last_name: VARCHAR(50), NOT NULL
  • date_of_birth: DATE, NOT NULL
  • email: VARCHAR(100), UNIQUE, NOT NULL
  • phone: VARCHAR(15), UNIQUE, NOT NULL
  • created_at: DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP
  • updated_at: DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Comments:
We store core patient information. Email and phone must be unique. Deleting a patient should optionally cascade to appointments.

Table: doctors

  • id: INT, Primary Key, Auto Increment
  • first_name: VARCHAR(50), NOT NULL
  • last_name: VARCHAR(50), NOT NULL
  • specialty: VARCHAR(50)
  • email: VARCHAR(100), UNIQUE, NOT NULL
  • phone: VARCHAR(15), UNIQUE, NOT NULL
  • working_hours: VARCHAR(100) -- Example: "Mon-Fri 09:00-17:00"
  • created_at: DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP
  • updated_at: DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Comments:
Doctors have unique contact info. Working hours help prevent overlapping appointments.

Table: appointments

  • id: INT, Primary Key, Auto Increment
  • doctor_id: INT, Foreign Key → doctors(id), NOT NULL
  • patient_id: INT, Foreign Key → patients(id), NOT NULL
  • appointment_time: DATETIME, NOT NULL
  • status: INT, NOT NULL DEFAULT 0 -- 0 = Scheduled, 1 = Completed, 2 = Cancelled
  • created_at: DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP
  • updated_at: DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Comments:
Each appointment links a patient and doctor. Doctor availability should be checked before scheduling.

Table: admin

  • id: INT, Primary Key, Auto Increment
  • username: VARCHAR(50), UNIQUE, NOT NULL
  • password_hash: VARCHAR(255), NOT NULL
  • role: VARCHAR(50), NOT NULL -- Example: "Receptionist", "Manager"
  • created_at: DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP
  • updated_at: DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Comments:
Admins manage the system. Passwords stored securely using hashing.

Table: payments

  • id: INT, Primary Key, Auto Increment
  • patient_id: INT, Foreign Key → patients(id), NOT NULL
  • amount: DECIMAL(10,2), NOT NULL
  • payment_date: DATETIME, NOT NULL
  • method: VARCHAR(50) -- Example: "Credit Card", "Cash"
  • status: VARCHAR(20) DEFAULT "Pending" -- "Pending", "Completed", "Failed"

Comments:
Tracks patient payments. Supports different payment methods and statuses.

MongoDB Collection Design

Collection: prescriptions

{
  "_id": "ObjectId('64abc123456')",
  "patientId": 101,
  "appointmentId": 51,
  "doctorId": 12,
  "medications": [
    {
      "name": "Paracetamol",
      "dosage": "500mg",
      "frequency": "Every 6 hours",
      "duration_days": 5
    },
    {
      "name": "Ibuprofen",
      "dosage": "200mg",
      "frequency": "Every 8 hours",
      "duration_days": 3
    }
  ],
  "doctorNotes": "Monitor temperature. Avoid strenuous activity.",
  "tags": ["fever", "pain"],
  "refillCount": 2,
  "pharmacy": {
    "name": "Walgreens SF",
    "location": "Market Street"
  },
  "created_at": "2026-01-01T10:00:00Z",
  "updated_at": "2026-01-01T10:00:00Z"
}