Inventory Management System ERD (Simple Version)

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

 

 

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.

 

 

 

PART 1) ITEMS

1A) ITEMS TABLE

COLUMN	DATA TYPE	NOTES
item_sku	VARCHAR	Primary Key.
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 item_quantity.

• Some smart alecks may be thinking “just create a SUM(QUANTITY) view from the movement table 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_quantity field that is “manually updated with code”. This can be a programming language or a stored SQL function.

 

1B) SUPPLIER TO ITEMS TABLE

COLUMN	DATA TYPE	NOTES
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

COLUMN	DATA TYPE	NOTES
supplier_id	INTEGER	Primary Key.
supplier_name	VARCHAR	Supplier’s name.
supplier_email	VARCHAR	Supplier’s email.
supplier_tel	VARCHAR	Supplier’s telephone number.
supplier_address	VARCHAR	Supplier’s address.

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

COLUMN	DATA TYPE	NOTES
user_id	INTEGER	Primary Key.
user_name	VARCHAR	User’s name.
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

COLUMN	DATA TYPE	NOTES
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.

 

 

 

LINKS & REFERENCES

• “Storage Boxx” PHP Inventory System – Code Boxx
• Entity-Relationship Diagram Symbols and Notation – Lucid Chart