Pharmacy management system project
batches, expiry dates, bills and an ER diagram

Owner

Manages medicines, suppliers and prices, sees every report.

Pharmacist

Makes sales bills and records purchases; cannot change prices or delete records.

Seven entities. Stock is held per batch, not per medicine: a medicine's stock is the sum of its unexpired batches. That one decision explains most of the design.

Relationships

• supplier supplies purchase (one to many)
• purchase brings in batch (one to many)
• medicine is stocked as batch (one to many)
• sale contains sale_item (one to many)
• batch is sold in sale_item (one to many)
• user makes sale (one to many)

Mermaid source

Paste it into mermaid.live, draw.io (Arrange, Insert, Advanced, Mermaid) or any Markdown editor that draws Mermaid to get the diagram for your report.

erDiagram
  SUPPLIER ||--o{ PURCHASE : "supplies"
  PURCHASE ||--o{ BATCH : "brings in"
  MEDICINE ||--o{ BATCH : "is stocked as"
  SALE ||--o{ SALE_ITEM : "contains"
  BATCH ||--o{ SALE_ITEM : "is sold in"
  USER ||--o{ SALE : "makes"
  MEDICINE {
    uuid id PK
    text brand_name
    text generic_name
    text category
    text strength
    int reorder_level
  }
  SUPPLIER {
    uuid id PK
    text name
    text phone
    text licence_no
  }
  PURCHASE {
    uuid id PK
    uuid supplier_id FK
    text invoice_no
    date purchased_on
    numeric total
  }
  BATCH {
    uuid id PK
    uuid medicine_id FK
    uuid purchase_id FK
    text batch_no
    date expires_on
    int quantity
    numeric cost
    numeric mrp
  }
  SALE {
    uuid id PK
    text bill_no UK
    text customer_name
    date sold_on
    numeric discount
    numeric total
  }
  SALE_ITEM {
    uuid id PK
    uuid sale_id FK
    uuid batch_id FK
    int quantity
    numeric price
  }
  USER {
    uuid id PK
    text name
    text role
  }

This builds on Refinar's inventory starting point, which already has products, suppliers and a stock ledger with full history. Batches, expiry and sales bills come from the prompt. Running a real medical store? See the pharmacy management system page instead.

The prompt it starts with

A pharmacy management system for a final-year project, in English: medicines with brand name, generic name, category, strength and reorder level; suppliers; purchases from a supplier with batch number, expiry date, quantity and cost per batch; sales bills with customer name, items, quantity, price, discount and total, taking stock from the batch that expires first; expired batches cannot be sold; alerts for low stock and batches expiring within 60 days; daily and monthly sales reports. Roles: owner, who does everything, and pharmacist, who makes bills and records purchases.

• Returns to supplier for expired batches, with a credit note.
• Prescription upload for medicines that need one.
• Barcode search at the counter.
• Tax lines on the bill, as your country's rules require.

• Hospital management systemPatients, doctors, appointments, prescriptions, wards and bills, with a login per role.
• Library management systemCatalogue, copies, issue and return with due dates, fines and reservations.
• Student management systemStudents, courses, attendance, marks and results, fees and a student login.
• Hostel management systemRooms and beds, allocation, fees, out-passes, visitors and complaints.
• E-commerce websiteStorefront, cart, checkout, orders with status, customer accounts and a stock admin.
• Event management systemEvents with ticket types, registration, QR check-in at the door and feedback.