Pharmacy Management System (PMS)

1. Project Explanation

The Pharmacy Management System is a relational database project designed to digitize and streamline the daily operations of a retail pharmacy. In the healthcare and retail sector, managing the flow of medicine, tracking expiration dates, and maintaining accurate patient records is critical.

This project moves away from manual or disconnected record-keeping systems, offering a centralized database solution built on MSSQL. The system is designed to handle the core interactions between Products (medicines), Patients (customers), Employees (staff), and Suppliers. It ensures that every pill sold is accounted for, every prescription is linked to the correct patient, and inventory levels are updated in real-time.

2. Purpose of the Project

The primary purpose of this database is to ensure data integrity and operational efficiency. Key objectives include:

• Inventory Control & Safety: To strictly monitor stock levels and, more importantly, track expiry dates of medicines to prevent the sale of expired products.
• Sales Tracking: To record every transaction in detail (who sold it, to whom, which items, and at what price) using a normalized structure (Sale and Sale_Details).
• Patient Health Records: To maintain a history of patients and their prescriptions, allowing pharmacists to serve patients better and adhere to legal regulations.
• Financial Reporting: To enable the pharmacy owner to generate instant reports on monthly revenue and tax calculations via Stored Procedures.

3. Project Details and Technical Scope

The project was developed in four distinct steps, evolving from a basic schema to a fully functional relational database.

• Database Structure: The system consists of 10 normalized tables.
  • Main Entities: Patient, Product, Employee, Supplier.
  • Transactional Entities: Sale, Sale_Details, Prescription, Invoice.
  • Helper/Lookup Tables: Categories, JobTitles, City.
• Relationships: Strong One-to-Many (1:N) relationships are established. For example, one Sale can contain multiple items (Sale_Details), and one Supplier can provide multiple Products.
• Automation: The project utilizes Stored Procedures (e.g., sp_UpdateStock) to automate critical tasks like reducing inventory count immediately after a sale is recorded.
• Reporting: Complex SQL queries and Views (e.g., vw_DetailedSales) are created to join multiple tables and present meaningful insights to the user.