Stock Management System Database Design

Submitted by oretnom23 on Saturday, June 4, 2022 - 11:46.

This is a Database Design for a Stock Management System. This Database Design is the one I used for developing Stock Management System Project. The stock management system manages the business stocks or inventory. The said project is useful for storing and retrieving the purchase orders, receiving orders, back orders, return orders, and sales transactions.

Here, I will show you

Stock Management System ERD

This article also contains the Entity Relationship Diagram. This article will help you an idea of how the Stock Management System backend manages the data.

The Stock Management System Database consists of 12 tables. It has the Supplier, Item, Purchase Orders (PO), PO Items, Receiving, Back Orders, BO Items, Returns, Stocks, Sale, Users, and Users Tables. Some of the tables are only for the system information only and are not related or connected to the other tables.

Stock Management System Database Tables

Supplier Table

The Supplier Table stores the list of the shop's or store's suppliers. The tables contain the basic information about the suppliers.

Table Name: supplier_list

Field Name Description Type Length
id (PK) Supplier Table's Primary Key int 30
name Supplier Name TEXT  
address Supplier Address TEXT  
cperson Supplier's Contact Person TEXT  
contact Supplier Contact Person's Contact Number TEXT  
status Supplier Status [1=Active, 0=Inactive] tinyint 1
date_created (CURRENT_TIMESTAMP) Date and Time does product added datetime  
date_updated (CURRENT_TIMESTAMP) Date and Time of product last update datetime  

Items Table

The Items Table stores all store or shop's products. This table contains the columns for the product information.

Table Name: supplier_list

Field Name Description Type Length
id (PK) Position ID int 30
supplier_id (PK) Product Supplier's ID int 30
name Item/Product Name text  
description Product/Item description text  
cost Item/Product Cost or Price float  
status Product/Item's Status [1=Active, 0=Inactive] tinyint 1
date_created (CURRENT_TIMESTAMP) Date and Time does product added datetime  
date_updated (CURRENT_TIMESTAMP) Date and Time of product last update datetime  

Purchase Order (PO) Table

The Purchase Order table store the list of the store's orders to their supplier. This table must be populated first in order to receive orders or products as the additional stock of the item. This table contains only the order information and item details are listed in another table.

Table Name: purchase_order_list

Field Name Description Type Length
id (PK) Purchase Order ID int 30
supplier_id (FK) Supplier ID int 30
po_code Purchase Order Reference Code varchar 50
amount Total Payable Amount of the Order float  
discount_perc Percentage of the Order Discount float  
discount Discount Amount float  
tax_perc Tax Percentage of the Order float  
tax Tax Amount float  
remarks Order other information float  
status Product/Item's Status [1=Active, 0=Inactive] tinyint 1
date_created (CURRENT_TIMESTAMP) Date and Time does product added datetime  
date_updated (CURRENT_TIMESTAMP) Date and Time of product last update datetime  

Purchase Order Items Table

The Purchase Order Items table store the list of the PO's Products to order. This table contains the product order details.

Table Name: po_items

Field Name Description Type Length
po_id (FK) Purchase Order ID int 30
item_id (FK) Item ID int 30
quantity Quantity of the product to order int 50
price Current Price of the Item to order float  
unit Current Price of the Item to order varchar 50
total Total price or cost of the product float  

Receiving Order Table

The Receiving Order table store the list of the stores that received orders from their purchase order. The table will be filled when the PO has received partially or in full. The table contains the receiving details.

Table Name: receiving_list

Field Name Description Type Length
id (PK) Receiving ID int 30
form_id Purchase Order ID or Back Order ID int 30
from_order Determines if the received product is from the Purchase Order or Back Oder. [1= PO, 2=BO] tinyint 1
amount Total Amount of the Order float  
discount_perc Percentage of the Order Discount float  
discount Discount Amount float  
tax_perc Tax Percentage of the Order float  
tax Tax Amount float  
stock_ids List of Stock ID float  
remarks Order other information float  
date_created (CURRENT_TIMESTAMP) Date and Time does product added datetime  
date_updated (CURRENT_TIMESTAMP) Date and Time of product last update datetime  

Back Order (BO) Table

The Back Order table stores the main information of the back order. Back Order data are only generated when PO or previous BO has been received partially. It contains the data about the remaining product to receive from PO.

Table Name: back_order_list

Field Name Description Type Length
id (PK) Back Order ID int 30
receiving_id (FK) Receiving Order ID int 30
po_id (FK) Purchase Order ID int 30
supplier_id (FK) Supplier ID int 30
bo_code Back Order Reference Code varchar 50
amount Total Payable Amount of the Order float  
discount_perc Percentage of the Order Discount float  
discount Discount Amount float  
tax_perc Tax Percentage of the Order float  
tax Tax Amount float  
remarks Order other information float  
status Product/Item's Status [1=Active, 0=Inactive] tinyint 1
date_created (CURRENT_TIMESTAMP) Date and Time does product added datetime  
date_updated (CURRENT_TIMESTAMP) Date and Time of product last update datetime  

Back Order Items Table

The Back Order Items table store the list of the BO's Products to order. This table contains the remaining product to receive details.

Table Name: bo_items

Field Name Description Type Length
bo_id (FK) Back Order ID int 30
item_id (FK) Item ID int 30
quantity Quantity of the product int 50
price Current Price of the product float  
unit Current Price of the product varchar 50
total Total price or cost of the product float  

Return Table

The Return table stores the data about the returned product due to some instance. It contains the product information to return and the reason why the store is returning the items.

Table Name: return_list

Field Name Description Type Length
id (PK) Return ID int 30
supplier_id (FK) Supplier ID int 30
return_code Return Order Reference Code varchar 50
amount Total Payable Amount of the Order float  
remarks Order other information float  
stock_ids List of Stock ID to return text  
date_created (CURRENT_TIMESTAMP) Date and Time does product added datetime  
date_updated (CURRENT_TIMESTAMP) Date and Time of product last update datetime  

Stock Table

The Stock table stores the data about items stocked in and out. This table can help you calculate the available product stock.

Table Name: stock_list

Field Name Description Type Length
id (PK) Stock ID int 30
item_id (FK) Item ID int 30
quantity Product Quantity int 30
unit Product Unit varchar 50
price Product Price float  
total Product Total Price text  
type Stock Type (1=In, 2=Out) tinyint 1
date_created (CURRENT_TIMESTAMP) Date and Time does product added datetime  

Sales Table

The Sales table stores the data of the sales transactions.

Table Name: stock_list

Field Name Description Type Length
id (PK) Sale ID int 30
sales_code Sale Transaction Code varchar 50
client Client Name text  
amount Total amount payable float  
remarks Sale Transaction's other information text  
stock_ids List of Stock ID text  
date_created (CURRENT_TIMESTAMP) Date and Time does product added datetime  
date_updated (CURRENT_TIMESTAMP) Date and Time of product last update datetime  

System Information Table

The System Information table stores the data about the system. It contains the dynamic information of the system such as the system name, logo, etc.

Table Name: stock_list

Field Name Description Type Length
id (PK) Information ID int 30
meta_field/td> Data Field Name text  
meta_value Data Field Value text  

Lastly, the Users Table. This table doesn't have any relation with the other tables above. This table only stores the system users' credentials.

Users Table

Field Name Description Type Length
id (PK) User ID int 11
firstname User First Name TEXT  
middlename User middle Name TEXT [default: NULL]  
lastname User Last Name TEXT  
firstname User First Name TEXT  
username Username TEXT  
password User Password TEXT  
avatar User image path TEXT  
type User Type (1 = Administrator, 2 = Staff) tinyint 1
date_added (CURRENT_TIMESTAMP) The date and Time of the user have been created datetime  
date_updated (CURRENT_TIMESTAMP) Date and Time of user details last update datetime  

That's it! You can use this Database Design for your Stock Management System as a reference or use it to develop your own Stock/Inventory Management Application.

To check how this Stock Management System Database Design works, you can click the link below to redirect to the actual project source code:
Stock Management System using PHP/OOP and MySQL Database Source Code

I hope this will you with what you are looking for.

Thanks!

Tags

Add new comment

About text formats
  • 21558 views