Decision Support System (DSS): Demo PoS for a Retail Store
In this tutorial, we will create a simple point of sale system for a fictitious retail store. The system will be used by the operational staff to record the day to day business transactions, the tactical staff will also use the system to monitor the retail store's current performance.
The system will have the ability to export data to excel. Excel will be used as a decision support system tool to analyze the data. The data in Excel can also be combined with data from other sources.
Retail store MIS components
1. Business Procedures
Business procedures define how the day to day business transactions must be conducted. The point of sale system will be used to record daily sales.
The following list outlines the activities to be done when a customer buys an item
2. People
The users who will be using the system will be cashiers, accountants and inventory control users. All the users must be computer literally and be trained on how to use the point of sale system.
3. Hardware
The point of sale system will Microsoft Access as the database engine. Microsoft access is a standalone database engine and as such, the system will be run on a single computer. The computer used will need to have the following specifications.
4. Software
The computer that will be used with the system should have the following minimum specifications.
5. Data
The day to day business transactions will be stored in a Microsoft Access database.
Point of Sale Database Dictionary
A database dictionary describes the database fields, what they do and the data type.
The following describes the tables that we will have in our database. For the sake of simplicity, we will keep the number of tables to only four (4).
Products Table
S/N |
Field Name |
Data Type |
Description |
1 |
id |
Numeric |
Record primary key |
2 |
Product_name |
String |
Name of the product |
3 |
Price |
Numeric |
The price of the product |
4 |
Quantity_at_hand |
Numeric |
Available quantity at hand on any given time |
5 |
Recommended_supplier_id |
Numeric |
Foreign key that links to suppliers table |
Customers Table
S/N |
Field Name |
Data Type |
Description |
1 |
id |
Numeric |
Record primary key |
2 |
Customer_name |
String |
Name of the customer |
3 |
Total_purchases |
Numeric |
Accumulated value of the total sales to the particular customer |
Suppliers Table
S/N |
Field Name |
Data Type |
Description |
1 |
id |
Numeric |
Record primary key |
2 |
Supplier_name |
String |
Name of the customer |
3 |
Contact_number |
Numeric |
Contact number for the supplier |
4 |
Email_address |
String |
Email address for the supplier |
Sales Table
S/N |
Field Name |
Data Type |
Description |
1 |
id |
Numeric |
Record primary key |
2 |
Customer_id |
Numeric |
Customer unique identifier |
3 |
Transaction_date |
Date |
The date that the transaction was recorded |
4 |
Amount_paid |
Numeric |
Total purchase amount |
5 |
Paid |
Boolean |
Boolean field that shows whether a customer has paid or not |
Sales Details Table
S/N |
Field Name |
Data Type |
Description |
1 |
id |
Numeric |
Record primary key |
2 |
sales_id |
Numeric |
Customer unique identifier |
3 |
Product_id |
Date |
The date that the transaction was recorded |
4 |
quantity |
Numeric |
Total purchase amount |
5 |
Paid |
Boolean |
Boolean field that shows whether a customer has paid or not |
Point of Sale database entity relationship diagram
The entity relationship diagram shows the relationship between tables in the database. The following table describes the relationships between the database tables
S/N |
Relationship |
Primary |
Foreign key |
Relationship nature |
1 |
Products to Sales |
Id in products |
Product_id in sales |
1 to Many |
2 |
Sales to customers |
Id in customers |
Customer_id in sales |
Many to Many |
3 |
Products to suppliers |
Id in suppliers |
Recommended_supplier_id in products |
Many to 1 |
The following image shows the relationships among the tables in our database.
How a transaction is processed in the system
1. product quantity at hand in products table
2. updates the amount paid field and marks the transaction as paid in the sales table
3. updates the total purchases field in customers table
Point of Sale Graphical User Interface Screenshots
System main window – this is the entry point into the system. It allows the users to enter data, process transactions, print reports and export sales data to Excel for further analysis.
Here we have demonstrated various data entry window used during POS, to give an overview of the field that is filled while performing the transaction.
Point of Sale Window used by cashiers at the cash point
Customer data entry window
Products data entry window
Summary
This tutorial shows you the practical steps involved in developing an information system and explained a prototype of a POS (point of sale) system. The system has been developed using Visual Basic for Applications (VBA) in Microsoft Access.