Pink Patrol

Entity Relationship Diagram

The Diagram

Descriptive documentation and argumentation

The designed database represents an Engineering Project Management System in the healthcare domain, integrating hospital operations, patient management, medical processes, and pharmaceutical workflows. The system is structured to support real-world activities such as scheduling appointments, recording diagnoses, managing prescriptions, and handling pharmacy sales. The model follows a relational approach, ensuring data integrity, scalability, and efficient querying.

Core Entities and Their Roles

• Hospital, Department, and Specialization

The system models the organizational structure through Hospital, Department, and Specialization entities.

A Hospital contains multiple departments.

Each Department is associated with a specific Specialization (e.g., Cardiology, Neurology).

This separation allows flexibility: The same specialization can exist in multiple departments.Departments can be managed independently.

• Doctor

The Doctor entity stores personal and professional information such as name, contact details, and specialization.

Each doctor is linked to a specialization.

The relationship Doctor–Department is modeled as a many-to-many relationship through an associative table, allowing: A doctor to work in multiple departments. Departments to have multiple doctors.

This reflects real hospital environments.

• Patient

The Patient entity captures demographic and contact information.

This entity is central to the system and connects to: Appointments. Diagnoses. Laboratory tests. Prescriptions.Pharmacy transactions.

This ensures a complete medical history for each patient.

Medical Process Entities

• Appointment

The Appointment entity represents scheduled interactions between patients and doctors.

It includes: Date. Status (scheduled, completed, cancelled). Type (checkup, emergency, consultation). Priority level.

A recursive relationship (parent_appointment_id) allows modeling follow-up visits.

• Medical Examination and Laboratory Test

Medical_examination records clinical observations and notes. Laboratory_test stores diagnostic test results.

These entities: Are linked to both doctor and patient. Provide detailed medical insights. Reflect real diagnostic workflows.

• Diagnosis (ICD-based)

The system uses: ICD (standard medical classification). Patient_diagnosis.

This design ensures: Standardization of diagnoses. Ability to track multiple diagnoses per patient. Compatibility with real healthcare systems.

The Appointment_diagnosis table resolves the many-to-many relationship between examinations and diagnoses.

Pharmaceutical and Sales Subsystem

• Drug, ATC Code, and Producers

Drug represents medications

ATC_code classifies drugs

Drug_producers stores manufacturer data

This structure supports: Drug traceability. Regulatory compliance. Classification-based queries.

• Pharmacy and Inventory

Pharmacy represents physical locations

Inventory tracks available drugs and quantities

Inventory_price stores price changes over time

This allows: Stock management. Historical price tracking. Realistic pharmacy operations.

• Prescription and Sales

Prescription connects doctor, patient, and medication

Pharmacy_sale records completed transactions

Sale_item details individual items within a sale

This separation ensures: Accurate billing. Traceability of sold drugs. Support for multi-item transactions.