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

 

ER DIAGRAM

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.

 

 

TABLE DETAILS

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

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.

 

THE END

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!

1 thought on “Inventory Management System ERD (Simple Version)”

  1. thanks for sharing your code, its been great to stepping into php mysql.
    Please keep your scripts coming.
    all the best

Leave a Comment

Your email address will not be published. Required fields are marked *