database structure for the UPPCL medical process portal.

This structure is designed to support

the core functionalities and workflows required for Medical Reimbursement, Medical
Cashless Treatment, and Medical Advance.

1. Users Table

 Stores information about employees, approvers, and administrators.

Column Type Description

user_id INT (PK) Unique identifier for each user
username VARCHAR Username for login
password_hash VARCHAR Hashed password for security
role ENUM Role of the user (e.g., employee, approver)
name VARCHAR Full name of the user
email VARCHAR Email for notifications
phone VARCHAR Contact phone number
created_at TIMESTAMP Account creation timestamp
updated_at TIMESTAMP Last update timestamp

2. Patients Table

 Stores information on patients (self or dependents) linked to each user.

Column Type Description

patient_id INT (PK) Unique identifier for each patient
user_id INT (FK) Foreign key linking to the Users table
name VARCHAR Name of the patient
relationship ENUM Relationship to the user (self, spouse, child)
dob DATE Date of birth of the patient
gender ENUM Gender of the patient
created_at TIMESTAMP Record creation timestamp
updated_at TIMESTAMP Last update timestamp

3. Applications Table

 Stores application details for medical reimbursement, cashless treatment, and medical
advance.

Column Type Description

application_id INT (PK) Unique identifier for each application
user_id INT (FK) Foreign key linking to the Users table
patient_id INT (FK) Foreign key linking to the Patients table
application_type ENUM Type of application (reimbursement, cashless, advance)
 Column Type Description
Status of the application (pending, approved, rejected,
status ENUM
completed)
total_amount DECIMAL Total amount requested
created_at TIMESTAMP Application submission timestamp
updated_at TIMESTAMP Last update timestamp

4. Hospitals Table

 Stores information about hospitals, categorized as Government, Empaneled, or Non-

Empaneled.

Column Type Description

hospital_id INT (PK) Unique identifier for each hospital
name VARCHAR Name of the hospital
type ENUM Type of hospital (government, empaneled, non-empaneled)
address TEXT Address of the hospital
contact_info VARCHAR Contact information
empanelment_status BOOLEAN Whether the hospital is empaneled

5. Documents Table

 Stores uploaded documents related to applications, such as medical certificates and bills.

Column Type Description

document_id INT (PK) Unique identifier for each document
application_id INT (FK) Foreign key linking to the Applications table
document_type ENUM Type of document (medical certificate, bill, doctor’s letter, etc.)
file_path VARCHAR File path or URL to the stored document
uploaded_at TIMESTAMP Document upload timestamp

6. Approvals Table

 Tracks approval stages for each application and logs actions taken by each approver.

Column Type Description

approval_id INT (PK) Unique identifier for each approval action
application_id INT (FK) Foreign key linking to the Applications table
approver_id INT (FK) Foreign key linking to the Users table
approval_stage ENUM Stage of approval (unit head, GM/Medical, final approval)
status ENUM Approval status (pending, approved, rejected)
comments TEXT Comments or reasons for approval/rejection
timestamp TIMESTAMP Approval action timestamp
7. Notifications Table

 Stores notifications for updates or actions related to applications.

Column Type Description

notification_id INT (PK) Unique identifier for each notification
user_id INT (FK) Foreign key linking to the Users table
application_id INT (FK) Foreign key linking to the Applications table
message TEXT Notification message content
read_status BOOLEAN Whether the notification has been read
created_at TIMESTAMP Notification creation timestamp

8. Audit Log Table

 Logs changes and actions within the system for compliance and debugging.

Column Type Description

log_id INT (PK) Unique identifier for each log entry
user_id INT (FK) Foreign key linking to the Users table
action VARCHAR Description of the action performed
details TEXT Additional details about the action
timestamp TIMESTAMP Action timestamp

9. ERP Integration Table

 Manages ERP integration for approved applications that move to payment processing.

Column Type Description

erp_id INT (PK) Unique identifier for each ERP transaction
application_id INT (FK) Foreign key linking to the Applications table
erp_reference VARCHAR ERP transaction reference ID
status ENUM Status of the ERP transaction (pending, completed)
processed_at TIMESTAMP Date and time when the transaction was processed

Relationships Between Tables

1. Users ↔ Patients: One user can have multiple dependents (patients).

2. Users ↔ Applications: Each application is submitted by a user.
3. Applications ↔ Hospitals: Each application is associated with a selected hospital.
4. Applications ↔ Documents: Each application may have multiple uploaded documents.
5. Applications ↔ Approvals: Each application passes through multiple approval stages.
6. Applications ↔ Notifications: Notifications are sent regarding the status of an
application.
 7. Applications ↔ ERP Integration: Approved applications are processed through ERP
for payment.

This structure should cover the basic functionality required for the portal, including user
authentication, application submission, approval workflow, document management,
notifications, and ERP integration. Each table is designed to be flexible and scalable, allowing
the addition of more features if needed. Let me know if you need more details on specific tables
or relationships!