Welcome to a quick sharing of an ERD of an inventory management system. I am pretty sure there are already plenty of such “free inventory ER diagrams” everywhere on the Internet. So instead of going in the same direction, here’s my simple version for beginners. Hope this helps to speed up your design process. Read on!
TABLE OF CONTENTS
First, here is the entire ER diagram and a quick overview of the system.
INVENTORY MANAGEMENT SYSTEM
Yes, this is not a “brain damage system structure”. I have generalized it into 3 groups of entities:
- Items – Captain Obvious at your service. This is an inventory management system, of course, there are inventory items involved.
- Suppliers & Users – The “people” and “companies”.
- Movement – To keep track of the stock movement.
FOR REFERENCE ONLY
I can hear the dumb trolls knocking on the door while screaming “this diagram is not accurate”. So here’s a quick disclaimer on the relationship between the entities – This is only for reference and it is subjective.
- An item can be obtained from different suppliers. If you decide that “an item is specific to a supplier”, then it is a one-to-one relationship. In this case, feel free to remove the “supplier-items” table, and directly add “supplier ID” to the items table.
- An item will definitely have at least one movement entry – The initial stock take while entering into the system. If you decide that this is not necessary, then it is a “zero or many” relationship.
You are building your own system here. You make your own changes and decide your own rules.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) ITEMS
1A) ITEMS TABLE
|item_name||VARCHAR||The item name.|
|item_description||VARCHAR||Optional, item description.|
|item_unit||VARCHAR||Unit of measurement.|
|item_quantity||DOUBLE||The current remaining stock.|
This table should be pretty self-explanatory, and the only “problematic” part here is the
- Some smart alecks may be thinking “just create a
SUM(QUANTITY)view from the
movementtable to get the current stock levels”.
- Yep, it works. But as entries pile up over the years, it also becomes very inefficient.
- So it makes more sense to have a
item_quantityfield that is “manually updated with code”. This can be a programming language or a stored SQL function.
1B) SUPPLIER TO ITEMS TABLE
|item_sku||VARCHAR||Primary and foreign key.|
|supplier_id||INTEGER||Primary and foreign key.|
Does this need an explanation? Which supplier offers which items.
PART 2) SUPPLIERS & USERS
2A) SUPPLIERS TABLE
|supplier_tel||VARCHAR||Supplier’s telephone number.|
This should cover all the “basic information” about suppliers. Feel free to add more yourself if any is missing, or remove those that you don’t use.
2B) USERS TABLE
|user_email||VARCHAR||Unique, user’s email address.|
|user_password||VARCHAR||User’s password. Encrypt in your own system.|
Once again, this should capture all the basic user information. Feel free to add more as required – Address, telephone, gender, role, etc…
3) MOVEMENT TABLE
|item_sku||VARCHAR||Primary and foreign key. Item moved.|
|user_id||INTEGER||Primary and foreign key. The user who added this movement entry.|
|move_date||DATE TIME||Primary key. Date and time when the stock is being moved.|
|move_direction||VARCHAR||You decide. Personally, I will go with just “in”, “out”, and stock “take”.|
|move_quantity||DOUBLE||The quantity moved.|
|move_notes||VARCHAR||Optional notes on the movement entry.|
Lastly, use this table to store all the stock movement.
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!