Grocery Shrink Management System
Category: Database System Design
Gallery
Having spent a lot of time working in grocery retail, one thing I’ve learned is this: Shrink is a pain.
Shrink is the items found in a grocery store that have reached the end of their shelf-life. It is the job of the grocery team to identify the shrink, and take them out of inventory. The problem I noticed during my time as a grocery stocker was this: identifying these items is a huge pain. There’s no way to know exactly what needs to be checked, so every day, you have to look at every single item and determine its shelf-life.
Most packaged items come from their vendor with a date on them. But in a department of hundreds, if not thousands of items? This is quite tedious. We as humans could hardly be capable of remembering every single item’s shelf life.
But what if we did not need to remember? And instead, could consult a database to find out exactly what we need to know.
This is what I had in mind during my Database System Design class. I was tasked with designing a relational database system with real world application. Complete with an ER Diagram, SQL database schema, and a write-up of what exactly the software would do.
Here is a quick breakdown. The full report is viewable here
Each department would place an order to the warehouse for their various needs. Departments would be Produce, Dairy, Meat, Frozen, and Dry goods. Other departments exist of course, but these are the departments primarily responsible for handling shrink.
Upon placing these orders, the warehouse will ship the ordered items to the store. These items will have individual IDs signifying their shelf-life. Most items ship in batches which have equal shelf lives. For example, a box of milk jugs will all have the same date.
When the items arrive, they would be scanned in by the receiving store. This ensures only items that actually arrived at the store are entered into the database. As it sometimes occurs that an item is ordered, but the warehouse is out of that item at the time. We would not want items to be part of the shrink-management system they are not actually part of the inventory.
After being scanned in, the items would join both the regular inventory database as well as the shrink database. The shrink ID would define what department the item is in, as well as the time it has on the shelf.
This system could then be consulted by employees tasked with managing shrink and needing to know what is to be taken off the shelf.
If implemented, I believe this system would accomplish two things:
1. Provide an easier experience to employees tasked with removing shrink from the inventory set.
2. Ensure a higher overall customer satisfaction rate, as the risk of purchasing an expired item is greatly lowered.
This system was very fun to design, and I learned a lot while doing it. It was extremely engaging to learn by designing an prototype with real-world application, and I hope to do it again in the future.
Perhaps one day, I will get to design a real, working product used in the field. But until then, I will keep learning every day.
Shrink is the items found in a grocery store that have reached the end of their shelf-life. It is the job of the grocery team to identify the shrink, and take them out of inventory. The problem I noticed during my time as a grocery stocker was this: identifying these items is a huge pain. There’s no way to know exactly what needs to be checked, so every day, you have to look at every single item and determine its shelf-life.
Most packaged items come from their vendor with a date on them. But in a department of hundreds, if not thousands of items? This is quite tedious. We as humans could hardly be capable of remembering every single item’s shelf life.
But what if we did not need to remember? And instead, could consult a database to find out exactly what we need to know.
This is what I had in mind during my Database System Design class. I was tasked with designing a relational database system with real world application. Complete with an ER Diagram, SQL database schema, and a write-up of what exactly the software would do.
Here is a quick breakdown. The full report is viewable here
Each department would place an order to the warehouse for their various needs. Departments would be Produce, Dairy, Meat, Frozen, and Dry goods. Other departments exist of course, but these are the departments primarily responsible for handling shrink.
Upon placing these orders, the warehouse will ship the ordered items to the store. These items will have individual IDs signifying their shelf-life. Most items ship in batches which have equal shelf lives. For example, a box of milk jugs will all have the same date.
When the items arrive, they would be scanned in by the receiving store. This ensures only items that actually arrived at the store are entered into the database. As it sometimes occurs that an item is ordered, but the warehouse is out of that item at the time. We would not want items to be part of the shrink-management system they are not actually part of the inventory.
After being scanned in, the items would join both the regular inventory database as well as the shrink database. The shrink ID would define what department the item is in, as well as the time it has on the shelf.
This system could then be consulted by employees tasked with managing shrink and needing to know what is to be taken off the shelf.
If implemented, I believe this system would accomplish two things:
1. Provide an easier experience to employees tasked with removing shrink from the inventory set.
2. Ensure a higher overall customer satisfaction rate, as the risk of purchasing an expired item is greatly lowered.
This system was very fun to design, and I learned a lot while doing it. It was extremely engaging to learn by designing an prototype with real-world application, and I hope to do it again in the future.
Perhaps one day, I will get to design a real, working product used in the field. But until then, I will keep learning every day.