|  | Healthcare Industry | Medical Facilities

ER Diagram for Medical Store System: Entities, Relationships & Database Design

An Entity-Relationship (ER) diagram models the data structure for a medical store management system. Below is a normalized design capturing core entities and relationships.

Core Entities

Medicine

  • MedicineID (PK)
  • Name
  • Description
  • QuantityInStock
  • UnitPrice
  • ExpiryDate
  • Manufacturer
  • Category (OTC, Prescription, Controlled)

Customer

  • CustomerID (PK)
  • FirstName, LastName
  • PhoneNumber
  • Email
  • Address
  • DateOfBirth (for age-restricted medications)
  • InsuranceInfo (optional)

Employee

  • EmployeeID (PK)
  • FirstName, LastName
  • Role (Pharmacist, Technician, Cashier, Manager)
  • LicenseNumber (for pharmacists)
  • HireDate
  • ShiftSchedule

Sale (Transaction)

  • SaleID (PK)
  • CustomerID (FK)
  • EmployeeID (FK) - who processed sale
  • SaleDateTime
  • TotalAmount
  • PaymentMethod
  • PrescriptionID (FK, nullable)

SaleItem (Line Items)

  • SaleItemID (PK)
  • SaleID (FK)
  • MedicineID (FK)
  • Quantity
  • UnitPriceAtSale
  • Subtotal

Prescription

  • PrescriptionID (PK)
  • CustomerID (FK)
  • PrescriberID (FK - external doctor)
  • DateIssued
  • DateFilled
  • Status (Active, Filled, Expired, Cancelled)
  • RefillsRemaining

Supplier

  • SupplierID (PK)
  • Name
  • ContactInfo
  • LeadTimeDays

PurchaseOrder

  • POID (PK)
  • SupplierID (FK)
  • EmployeeID (FK - who ordered)
  • OrderDate
  • ExpectedDeliveryDate
  • Status

Key Relationships

  • Customer 1:M Sale (One customer makes many sales)
  • Sale 1:M SaleItem (One sale contains multiple items)
  • Medicine 1:M SaleItem (One medicine appears in many sale lines)
  • Employee 1:M Sale (One employee processes many sales)
  • Customer 1:M Prescription (One customer has many prescriptions)
  • Prescription 1:M Sale (One prescription can fill over multiple sales)
  • Supplier 1:M PurchaseOrder
  • PurchaseOrder 1:M PurchaseOrderItem (not shown, links to Medicine)

Design Notes

  • Separate Sale and SaleItem allows variable items per transaction
  • Prescription entity supports refill tracking and regulatory compliance
  • Consider adding InventoryAuditLog for stock discrepancy tracking
  • Indexes on MedicineID, CustomerID, SaleDateTime optimize common queries

Medical Facilities - Related Articles