Stock Management System Database Design
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
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!
Add new comment
- 23917 views