Accounts Management System Database Project using MS SQL C#

Radio City Accounts Management System Database Project using MS SQL C# application  with detail database design article and ERD Explanation

Radio City is one of the most popular and one of the largest digital audio/ video stores in Lahore. The store sells a vast collection of DVDs, CDs and LDs to a large number of customers. The approximate monetary value of the average daily sales of this store is more than many of its smaller competitors. Offering people the widest range to choose from, Radio City evidently monopolizes the market and is the first choice of most of the customers.

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.

 Source Code Link Here 

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”, “DVD”, “VHS”, “LD”, “Cassette”, “Other”
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