Convenience Store Sales and Inventory System Database Design Project
This Convenience Store Sales and Inventory System Database Design Project focuses on the store’s sales and inventory system.
Since they are using the manual recording of transactions, sometimes they forget to record the transaction of some customers.
Using a manual recording, there’s a high possibility of errors and miscomputations of sales like the record are being lost because of the busyness of the staff or the assigned personnel.
Since the system is a computerized one, it can be easy for the owner to manage the activities in their business.
Thus, this system can lessen the paper works in this area and it won’t be time-consuming to the owner and staff.
This system was created to be used in tracking the sales and inventory of the store.
It gives you the ability to check on your products, manage suppliers and many more.
It enables users to create, update and store products and transactions that are happening. In this system, a single transaction entry records necessarily details of the customer, products purchased, price and date while also updating inventory levels.
It is helpful to those who have a convenience store and that are using manual recording of the transaction. It also provides a computerized system for maintaining records of the products and the clients.
It is more efficient and reliable to use. Using this system, you can avoid human errors, data manipulations, and especially data inconsistency and redundancy.
It is user-friendly and it is used to improve efficiency in recording for each transaction.
Instead of maintaining separate record-keeping processes for the payment and inventory adjustment, you can manage each aspect with a single entry.
Every time there is a sales transaction, the sales and inventory system automatically updates the store’s database to reflect the corresponding requirement in terms of inventory and finance.
Data Dictionaries for Convenience Store Sales And Inventory System Database Design Project
Features:
- Inventory Control
- Sales Report
- Order Management
- Price List
These database tables below provide the entire database tables details such as Field Name, Descriptions, Data Types, Character Lengths.
Table 1: tblcustomer
| Field Name | Description | Type | Length |
| CUST_ID(PK) | Customer ID | INT | 5 |
| SALES_ID | Sales ID of the customer | INT | 5 |
Table 2: tblemployees
| Field Name | Description | Type | Length |
| EMPLOYEE_ID(PK) | Employee ID | INT | 5 |
| FIRST_NAME | Employee First Name | VARCHAR | 50 |
| LAST_NAME | Employee Last name | VARCHAR | 50 |
| Employee Email Address | VARCHAR | 50 | |
| PHONE NUMBER | Contact Number of the Employee | VARCHAR | 50 |
| JOB_ID | Employee’s Job | INT | 5 |
| ADDRESS | Employee’s Address | VARCHAR | 50 |
Table 3: tbljob
| Field Name | Description | Type | Length |
| JOB_ID(PK) | Job ID | INT | 5 |
| JOB_TITLE | Title of job | VARCHAR | 50 |
| SALARY | Monthly Salary | INT | 11 |
Table 4: tblproduct
| Field Name | Description | Type | Length |
| PRODUCT_ID(PK) | Product ID | INT | 5 |
| PRODUCT_NAME | Name of the product | VARCHAR | 50 |
| PROD_DESCRIP | Description of the product | VARCHAR | 50 |
| QTY_STOCK | Quantity of Stocks | INT | 11 |
| PRICE | Price of the product per piece | INT | 11 |
| ON_HAND | Products available on hand | INT | 11 |
| DATE_SUP | Date the products delivered | DATE | |
| SUPPLIER_ID | Supplier ID | INT | 5 |
Table 5: tblsupplier
| Field Name | Description | Type | Length |
| SUPPLIER_ID(PK) | Supplier ID | INT | 5 |
| COMPANY_NAME | Supplier’s Company Name | VARCHAR | 50 |
| ADDRESS | Address of the supplier | VARCHAR | 50 |
| PHONE_NUMBER | Contact Number of the supplier | VARCHAR | 50 |
Table 6: tbluser
| Field Name | Description | Type | Length |
| USER_ID(PK) | User ID | INT | 5 |
| USERNAME | Username of the user | VARCHAR | 50 |
| PASSWORD | Password of the user | VARCHAR | 50 |
| EMPLOYEE_ID | Employee ID | INT | 5 |
Table 7: tblsales
| Field Name | Description | Type | Length |
| SALES_ID(PK) | Sales ID | INT | 5 |
| DATE | Date of the transaction | DATE | |
| PRODUCT_ID | Product ID | INT | 5 |
| PRODUCT_NAME | Name of the product | 50 | 50 |
| QUANTITY | Quantity of product purchased | INT | 11 |
| TOTAL_AMOUNT | Total amount to be paid | INT | 11 |
| CHANGE | Change of the customer | INT | 11 |
| EMPLOYEE_ID | Employee ID who cater the transaction | INT | 5 |
ERD for Convenience Store Sales and Inventory System Database Design Project
Figure 1 Proposed CG Dungog Sales and Inventory Entity Relationship Diagram shows the system entity relationships in each entity and their supposed functions in each relationship.
