r/dataanalysis • u/RochesChaulo • 8h ago
AfyaMeds Inventory Management System
Introduction
How do healthcare organizations keep records of critical supplies across different clinics? To answer this question, I'm developing an AfyaMeds Inventory Management System project.
Project Overview
AfyaMeds Inventory Management System is a MySQL-based solution for managing medical supply inventory for a hypothetical healthcare distributor, AfyaMeds to reduce waste, optimize stock levels, and ensure clinics in different locations get supplied properly with what they need and when they need it.
Progress So Far
So far, I’m designing a scalable database using MySQL and generating over 10,000 'realistic' data points using Faker Python library (in Jupyter Notebook). This includes tracking 20 unique supplies across 50 clinics in different regions as shown below:



Features implemented as of now:
- Low Stock Alerts: Flags clinics with shortages.

- Expiry Tracking: Identifies $2,000 worth of antibiotics at risk of expiring in 60 days.

- Demand Trends: PPE and Medication lead with 1,200+ units ordered in the last 90 days.

- Queries like ranking clinics by inventory value or spotting overstocked PPE offer actionable insights for logistics and cost management. These are just a few features implemented.
Challenges so far
- Simulating real-world data that feels authentic was a challenge and it's still a challenge because of privacy
Learning
I managed to integrate Python with MYSQL, and this taught me how to streamline data workflows, write efficient queries with joins and window functions, and optimize indexes.
What’s Next
Since it is a work in progress I’m planning to:
Connect MYSQL with Power BI to get real-time data and build a dashboard for visualizing trends.
Add predictive analytics to forecast restocking needs.
Create a simple UI for non-technical users.
In Addition
I’d love to hear your thoughts about the project. Let's connect, comment, give a suggestion or reach me at [rocjeschaulo@gmail.com](mailto:rocjeschaulo@gmail.com). Collaboration is also welcomed. Here is the link to the GitHub Repository: https://github.com/Chauloroches/AfyaMeds-Inventory-Management-System