We plan to make an automatic accounts monitoring and inventory management system for this store which would cover both, the sales and stock aspect of this quite successful business.
The objectives behind the design and implementation of this system are as follows:
Ø An efficient and easy to use system that aids the store management in smooth running.
Ø An easy way to record daily sales
Ø A system that could automatically adjust stock levels according to sales transactions
Ø Automatic display and calculation of the total amount per transaction
Ø Alerts to indicate required re-ordering of the products that fall below the stock threshold levels
Ø Collective and individual views of payments due and those received.
Ø A summarized view of sales of the shop to access sales performance
Ø Contact details of all the suppliers as well as management and status of the orders made.
Ø A system that offers different levels of access to different employee ranks to minimize the chances of fraud.
On the accounts side, the system would handle deductions from the stock of items sold as well as calculation of bills and accumulated daily, monthly sales etc. On the stock side the system would be responsible for alerting when products need to be re-ordered and maintain status of order and payments to suppliers. Over all it would present various views to assess sales performance over different time spans
System Module
Stock Management System
The stock system would deal with all aspects of the store management related to stock handling. It would comprise of the following sub-modules:
Suppliers:
This would allow the manager to add details of new suppliers and hold contacts of all suppliers.
Payments:
This would comprise of all payments due to suppliers for stocks bought.
Orders:
This module would cover orders made to suppliers as well as status of those orders and their payment status as well
Stock:
The module would comprise of all items in stock, it would allow the manager to add new items to stock and to monitor all items already in stock
Sales Management System
This part of the system would deal with the front end sales of the system. It would consist of the following sub-modules:
Billing:
This would allow sales transactions to be added to the system along with automatic bill calculation and stock deductions.
Sales Trends:
This module would allow the manager to see various sales trends and accumulated sales according to different time durations. It would generate various reports for the owner to assess sales performance.
Users Management System
This part of the system would deal with adding/ deleting and editing main users of the system so different views and rights can be provided for them.
Users/Roles of the System
The system would operate in three separate dimensions for the three different users.
Sales Person:
The sales person would be able to only add sales transactions into the system. The products sold would be deleted from the stock and whenever a product is out of stock its sale would be blocked
Store Keeper:
The store keeper would be able to place orders to the suppliers. S/he would be able to add new supplier details as well as enter new products into the stock. The stock keeper would also be able to see the stock details.
Owner:
The owner of the store would be able to see value of daily transactions as well as payments due. The daily profit would also be accessible to the owner. In addition to the access rights of the store keeper and sales person the owner would be able to generate sales reports to view trends and make better purchase order decisions.
Forms, Queries and Reports
Forms:
- Add\Edit a Product
- User Management
- Add a Product
- Stock Management
- Add\Edit a Supplier
- Supplier Management
- Purchase Order
- Stock Purchases
- Sales Order
- Stock Sales
- Add\Edit User
- User Management
- Add\Edit Category
- Add\Edit Type
Queries:
· Products low in stock
· Suppliers supplying a particular product
· Accounts Payable
· General Ledger: credits and debits
· Sales Trends: per product, daily/weekly/monthly/yearly aggregations
· Product demand: by frequency or proportion of sales made comparative to other products
Reports:
- Daily Sales Report
- Monthly Sales Report
- Yearly Sales Report
- General Sales Report
- Daily Purchases Report
- Monthly Purchases Report
- Yearly Purchases Report
- General Purchases Report
Entity Relationship Diagram
Enhanced Entity Relationship Diagram
Tables Description
Examples are given below:
User
Table Name: User
Primary Key: Username
Purpose: Record details of every user in the system (User entity)
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
Username | varchar (100) | Not Null | Primary Key | |
FullName | varchar (100) | Not Null | ||
Password | varchar (50) | Not Null | Must be at least 3 characters | |
UserRole | varchar (10) | Not Null | Must be one of “Owner”, “Store Keeper”, “Sales Person” |
Product
Table Name: Product
Primary Key: ProductID
Purpose: To record details of various Products in the stock (Product entity)
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
ProductID | int (4) | Not Null | Primary Key | |
Name | varchar (100) | Not Null | ||
Description | varchar (200) | Null | ||
Type | varchar (20) | Not Null | Must be one of “CD”, “ | |
Category | varchar (20) | Not Null | Must be one of “Games”, “Movies”, “Songs”, “Software”, “Other” | |
Price | Float(8) | Not Null | Not less than 0 | |
Amount | Int(4) | Not Null | Not less then 0 or greater then 1000 |
Supplier
Table Name: Supplier
Primary Key: SupplierID
Purpose: To record details of suppliers of stock products (Supplier entity)
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
SupplierID | int (4) | Not Null | Primary Key | |
Name | varchar (100) | Not Null | ||
Address | varchar (100) | Not Null | ||
City | varchar (50) | Not Null | ||
Country | varchar (100) | Not Null | ||
Phone | varchar (25) | Null | ||
Email | varchar (50) | Null | ||
Contact Person | varchar (100) | Null |
SalesOrder
Table Name: SalesOrder
Primary Key: TransactionID
Purpose: To record details of Sales order transactions made
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
TransactionID | int (4) | Not Null | Primary Key | |
SaleDate | Date | Not Null | Must be in DD-MM-YYYY format | |
TotalCost | double (8) | Not Null | Not less than 0 |
SalesOrderDetail
Table Name: SalesOrderDetail
Primary Key: TransactionID & ProductID (composite key)
Purpose: To record item details of every Sales Order transaction
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
TransactionID | int (4) | Not Null | Foreign Key | |
ProductID | int (4) | Not Null | Foreign Key | |
Quantity | long(8) | Not Null | Quantity not less then 0 or greater then 100,000 | |
UnitPrice | double (8) | Not Null | Not less than 0 | |
NetCost | double (8) | Not Null | Not less than 0 |
PurchaseOrder
Table Name: PurchaseOrder
Primary Key: TransactionID
Purpose: To record details of Purchase order transactions made
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
TransactionID | int (4) | Not Null | Primary Key | |
PurchaseDate | Date | Not Null | Must be in DD-MM-YYYY format | |
SupplierID | int (4) | Not Null | Foreign Key | |
TotalCost | double (8) | Not Null | Not less than 0 | |
Status | varchar (20) | Not Null | Must be one of “Payment Pending”, “Shipment Pending”, “Completed” |
PurchaseOrderDetail
Table Name: PurchaseOrderDetail
Primary Key: TransactionID & ProductID (composite key)
Purpose: To record item details of every Purchase Order transaction
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
TransactionID | int (4) | Not Null | Foreign Key | |
ProductID | int (4) | Not Null | Foreign Key | |
Quantity | long(8) | Not Null | Quantity not less then 0 or greater then 100,000 | |
UnitPrice | double (8) | Not Null | Not less than 0 | |
NetCost | double (8) | Not Null | Not less than 0 |
ProductCategory
Table Name: ProductCategory
Primary Key: CategoryID
Purpose: To record the different categories of products
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
CategoryID | int (4) | Not Null | ||
CategoryName | Varchar(100) | Not Null |
ProductType
Table Name: PurchaseType
Primary Key: TypeID
Purpose: To record different types of products
Column Name | Data Type and Domain | Null Constraint | Key Constraint | Application based Constraint |
TypeID | int (4) | Not Null | ||
TypeName | Varchar(100) | Not Null |
Source Code Link Here
