Convenience Store Sales and Inventory System Database Design Project

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 NameDescriptionTypeLength
CUST_ID(PK)Customer IDINT5
SALES_IDSales ID of the customerINT5

Table 2: tblemployees

Field NameDescriptionTypeLength
EMPLOYEE_ID(PK)Employee IDINT5
FIRST_NAMEEmployee First NameVARCHAR50
LAST_NAMEEmployee Last nameVARCHAR50
EMAILEmployee Email AddressVARCHAR50
PHONE NUMBERContact Number of the EmployeeVARCHAR50
JOB_IDEmployee’s JobINT5
ADDRESSEmployee’s AddressVARCHAR50

Table 3: tbljob

Field NameDescriptionTypeLength
JOB_ID(PK)Job IDINT5
JOB_TITLETitle of jobVARCHAR50
SALARYMonthly SalaryINT11

Table 4: tblproduct

Field NameDescriptionTypeLength
PRODUCT_ID(PK)Product IDINT5
PRODUCT_NAMEName of the productVARCHAR50
PROD_DESCRIPDescription of the productVARCHAR50
QTY_STOCKQuantity of StocksINT11
PRICEPrice of the product per pieceINT11
ON_HANDProducts available on handINT11
DATE_SUPDate the products deliveredDATE 
SUPPLIER_IDSupplier IDINT5

Table 5: tblsupplier

Field NameDescriptionTypeLength
SUPPLIER_ID(PK)Supplier IDINT5
COMPANY_NAMESupplier’s Company NameVARCHAR50
ADDRESSAddress of the supplierVARCHAR50
PHONE_NUMBERContact Number of the supplierVARCHAR50

Table 6: tbluser

Field NameDescriptionTypeLength
USER_ID(PK)User IDINT5
USERNAMEUsername of the userVARCHAR50
PASSWORDPassword of the userVARCHAR50
EMPLOYEE_IDEmployee IDINT5

Table 7: tblsales

Field NameDescriptionTypeLength
SALES_ID(PK)Sales IDINT5
DATEDate of the transactionDATE 
PRODUCT_IDProduct IDINT5
PRODUCT_NAMEName of the product5050
QUANTITYQuantity of product purchasedINT11
TOTAL_AMOUNTTotal amount to be paidINT11
CHANGEChange of the customerINT11
EMPLOYEE_IDEmployee ID who cater the transactionINT5

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.

ERD of Convenience Store Sales And Inventory System Database Design Project
ERD of Convenience Store Sales And Inventory System Database Design Project

For More Database Design Examples

Leave a Comment