📋 ER Diagram Overview
EVEP Mobile Reflection Unit Entity-Relationship Diagram represents the complete database schema for the vision screening and glasses prescription management system. The diagram shows all entities, their attributes, and relationships that support the clinical workflow.
🎨 Legend & Color Coding:
Primary Key - Unique identifier
Foreign Key - Relationship reference
Required Field - Mandatory data
Optional Field - Optional data
🏗️ Core Entities
PATIENTS
Primary Entity
- patient_id (ObjectId) - Primary Key
- student_id (String) - School ID
- first_name (String) - First Name
- last_name (String) - Last Name
- date_of_birth (Date) - Birth Date
- gender (String) - Gender
- school_name (String) - School
- grade_level (String) - Grade
- parent_name (String) - Parent
- parent_phone (String) - Contact
- parent_email (String) - Email
- address (String) - Address
- registration_date (Date) - Reg Date
- medical_history (String) - History
- allergies (String) - Allergies
- created_at (Date) - Created
- updated_at (Date) - Updated
SCREENINGS
Primary Entity
- screening_id (ObjectId) - Primary Key
- patient_id (ObjectId) - Patient Reference
- medical_staff_id (ObjectId) - Staff Reference
- screening_date (Date) - Date
- screening_type (String) - Type
- left_eye_va (Number) - Left VA
- right_eye_va (Number) - Right VA
- both_eyes_va (Number) - Both VA
- left_eye_notes (String) - Left Notes
- right_eye_notes (String) - Right Notes
- screening_result (String) - Result
- recommendation (String) - Recommendation
- additional_notes (String) - Notes
- created_at (Date) - Created
- updated_at (Date) - Updated
PRESCRIPTIONS
Primary Entity
- prescription_id (ObjectId) - Primary Key
- patient_id (ObjectId) - Patient Reference
- screening_id (ObjectId) - Screening Reference
- medical_staff_id (ObjectId) - Staff Reference
- prescription_date (Date) - Date
- left_eye_sphere (Number) - Left Sphere
- left_eye_cylinder (Number) - Left Cylinder
- left_eye_axis (Number) - Left Axis
- right_eye_sphere (Number) - Right Sphere
- right_eye_cylinder (Number) - Right Cylinder
- right_eye_axis (Number) - Right Axis
- pd_distance (Number) - PD Distance
- prescription_type (String) - Type
- special_requirements (String) - Requirements
- status (String) - Status
- created_at (Date) - Created
- updated_at (Date) - Updated
DELIVERIES
Primary Entity
- delivery_id (ObjectId) - Primary Key
- patient_id (ObjectId) - Patient Reference
- prescription_id (ObjectId) - Prescription Reference
- inventory_id (ObjectId) - Inventory Reference
- delivery_date (Date) - Date
- school_name (String) - School
- delivery_address (String) - Address
- contact_person (String) - Contact
- contact_phone (String) - Phone
- delivery_status (String) - Status
- delivery_notes (String) - Notes
- signature_received (Boolean) - Signature
- received_by (String) - Received By
- created_at (Date) - Created
- updated_at (Date) - Updated
👥 Medical Staff Entities
MEDICAL_STAFF
Secondary Entity
- medical_staff_id (ObjectId) - Primary Key
- staff_id (String) - Staff ID
- first_name (String) - First Name
- last_name (String) - Last Name
- email (String) - Email
- phone (String) - Phone
- role (String) - Role
- specialization (String) - Specialization
- license_number (String) - License
- hire_date (Date) - Hire Date
- status (String) - Status
- created_at (Date) - Created
- updated_at (Date) - Updated
STAFF_CREDENTIALS
Secondary Entity
- credential_id (ObjectId) - Primary Key
- medical_staff_id (ObjectId) - Staff Reference
- credential_type (String) - Type
- credential_number (String) - Number
- issuing_authority (String) - Authority
- issue_date (Date) - Issue Date
- expiry_date (Date) - Expiry Date
- status (String) - Status
- document_url (String) - Document
- created_at (Date) - Created
- updated_at (Date) - Updated
STAFF_TRAINING
Secondary Entity
- training_id (ObjectId) - Primary Key
- medical_staff_id (ObjectId) - Staff Reference
- training_name (String) - Name
- training_type (String) - Type
- training_provider (String) - Provider
- training_date (Date) - Date
- expiry_date (Date) - Expiry
- certificate_number (String) - Certificate
- status (String) - Status
- notes (String) - Notes
- created_at (Date) - Created
- updated_at (Date) - Updated
📦 Inventory & Support Entities
INVENTORY
Support Entity
- inventory_id (ObjectId) - Primary Key
- item_code (String) - Item Code
- item_name (String) - Name
- category (String) - Category
- brand (String) - Brand
- model (String) - Model
- specifications (String) - Specs
- quantity_available (Number) - Available
- quantity_reserved (Number) - Reserved
- unit_price (Number) - Price
- supplier (String) - Supplier
- location (String) - Location
- status (String) - Status
- notes (String) - Notes
- created_at (Date) - Created
- updated_at (Date) - Updated
SCHOOLS
Support Entity
- school_id (ObjectId) - Primary Key
- school_code (String) - Code
- school_name (String) - Name
- school_type (String) - Type
- address (String) - Address
- city (String) - City
- district (String) - District
- contact_person (String) - Contact
- contact_phone (String) - Phone
- contact_email (String) - Email
- total_students (Number) - Students
- status (String) - Status
- created_at (Date) - Created
- updated_at (Date) - Updated
USERS
Support Entity
- user_id (ObjectId) - Primary Key
- username (String) - Username
- email (String) - Email
- password_hash (String) - Password
- role (String) - Role
- medical_staff_id (ObjectId) - Staff Reference
- is_active (Boolean) - Active
- last_login (Date) - Last Login
- created_at (Date) - Created
- updated_at (Date) - Updated
🔗 Entity Relationships
📊 Relationship Mapping:
PATIENTS → SCREENINGS
One-to-Many: One patient can have multiple screenings
Foreign Key: screening.patient_id → patient.patient_id
SCREENINGS → PRESCRIPTIONS
One-to-One: One screening can have one prescription
Foreign Key: prescription.screening_id → screening.screening_id
PRESCRIPTIONS → DELIVERIES
One-to-One: One prescription can have one delivery
Foreign Key: delivery.prescription_id → prescription.prescription_id
MEDICAL_STAFF → SCREENINGS
One-to-Many: One staff can perform multiple screenings
Foreign Key: screening.medical_staff_id → medical_staff.medical_staff_id
MEDICAL_STAFF → STAFF_CREDENTIALS
One-to-Many: One staff can have multiple credentials
Foreign Key: staff_credentials.medical_staff_id → medical_staff.medical_staff_id
MEDICAL_STAFF → STAFF_TRAINING
One-to-Many: One staff can have multiple training records
Foreign Key: staff_training.medical_staff_id → medical_staff.medical_staff_id
INVENTORY → DELIVERIES
One-to-Many: One inventory item can be used in multiple deliveries
Foreign Key: delivery.inventory_id → inventory.inventory_id
MEDICAL_STAFF → USERS
One-to-One: One staff can have one user account
Foreign Key: user.medical_staff_id → medical_staff.medical_staff_id
🔄 Clinical Pathway Flow
📋 Complete Clinical Workflow:
1
Patient Registration
Capture student and parent information, medical history, and contact details. Create patient record in PATIENTS table.
↓
2
Vision Screening
Medical staff conducts visual acuity testing for both eyes. Record results in SCREENINGS table with detailed measurements.
↓
3
Prescription Generation
Based on screening results, generate optical prescription with sphere, cylinder, and axis values for both eyes.
↓
4
Glasses Selection
Check inventory availability, select appropriate glasses based on prescription, and reserve items from INVENTORY table.
↓
5
Delivery Planning
Schedule delivery to school, coordinate with school contact, and create delivery record in DELIVERIES table.
↓
6
Delivery Execution
Deliver glasses to school, obtain signature confirmation, and update delivery status to completed.
↓
7
Follow-up & Monitoring
Track patient outcomes, schedule follow-up screenings, and maintain comprehensive patient care records.