Accounting and sales system Table design and ER Diagram

Here we shared Accounting and sales system Table design and ER Diagram. This system manages ice block sales, stock levels, customer billing, employee salaries, and pricing for an ice production business. The database includes tables to track sales, inventory, billing, customers, employees, and pricing. The project involves designing a database to manage financial and operational data for two distinct businesses: one is Focuses on managing tenants, rental properties, billing (electricity, rent, maintenance, service tax), and receipts. And another is Focuses on tracking sales, stock, billing, customers, employee salaries, and pricing for ice blocks.

---

Accounting and sales system Table design:

Tenant table
Field name	Data type	Constraints	Description
Tenant_id	Varchar(10)	Primary key	Id of the particular Tenant
Tenant_name	Varchar(25)	Not null	Name of the particular
Phone	Numeric(18,0)	Not null	Phone number Of the Tenant
E-mail	Varchar(50)	Not null	E-mail of the Tenant

Receipt Table
Field name	Data type	Constraints	Description
Receipt_no	Varchar(10)	Primary key	Number of the receipt
Period	Date	Not null	From &To_date of the receipt
Paid Amount	Numeric(10)	Not null	Amt paid by the Tenant
Bill_no	Varchar(10)	Foreign key	Bill Number
Particulars	Varchar(15)	Not null	Types of bill
Receipt_date	Date	Not null	Date on which Receipt is issued

Electricity Table
Field name	Data type	Constraints	Description
Bill_no	Varchar(10)	Primary key	Bill Number
Amount	Numeric(18,0)	Not null	Electricity Charges
Bill Date	Date	Not null	Date on which bill is prepared
Last_reading	Numeric(18,0)	Not null	Final Reading Of the Meter
First_reading	Numeric(18,0)	Not null	Initial Reading Of The Meter
Due_date	Date	Not null	Last day to pay the bill
KVA	Varchar(10)	Not null	Units allotted(Kilo Volts Ampere)
Tax	Numeric(18,0)	Not null	Tax allotted for the Electricity charges
Tenant_id	Numeric(18,0)	Foreign key	Id of the Tenant
Year	Numeric(18,0)	Not null	Year
Month	Varchar(10)	Not null	Month on which bill is issued

Rental table
Field name	Data type	Constraints	Description
Bill_no	Varchar(10)	Primary key	Bill Number
Bill Date	Date	Not null	Bill Date
Rental_amt	Numeric(18,0)	Not null	Rent Charges
Tenant_id	Varchar(10)	Foreign key	Id of the Tenant
Month	Varchar(10)	Not null	Month on which bill is issued
Year	Numeric(18,0)	Not null	Year

Maintenance table
Field name	Data type	Constraints	Description
Bill_no	Varchar(10)	Primary key	Bill Number
Bill Date	Date	Not null	Bill Date
Maintenance_amt	Numeric(18,0)	Not null	Maintenance Charges
Tenant_id	Varchar(10)	Foreign key	Id of the tenant
Month	Varchar(10)		Month on which bill is issued
Year	Numeric(18,0)		Year

Service Tax Table
Field name	Data type	Constraints	Description
Bill_no	Varchar(10)	Primary key	Bill Number
From_date	Date	Not null	Initial date of the tax
To_date	Date	Not null	Final date of the tax
Education_cess	Varchar(10)	Not null	Education_cess allotted to particular tax amount
Higher & Secondary Cess	Varchar(10)	Not null	Higher & Secondary Cess allotted to particular tax amount
Tax	Numeric(18,0)	Not null	Tax allotted to the rent
Tenant_id	Varchar(10)	Foreign key	Id of the Tenant
Bill_date	Date	Not null	Bill Date

Flat information Table
Field name	Data type	Constraints	Description
Door_no	Varchar(10)	Primary key	Door number of the Flat
Floor_no	Numeric(18,0)	Not null	Describes the particular floor
Rent	Numeric(18,0)	Not null	Rent of the flat
Meter_no	Numeric(18,0)	Not null	Meter number of the flat

Tenant &Flat  Information Table
Field name	Data type	Constraints	Description
Tenant_id	Varchar(10)	Foreign key	Id of the Tenant
Door_no	Varchar(10)	Foreign key	Number of the door

Indus Ice Plant:

Sales table
Field name	Data type	Constraints	Description
Customer_id	Varchar(10)	Foreign key	Id of the customer
Date	Date	Not null	Sales Date
Qty	Numeric(18,0)	Not  null	Number of ice blocks sold

Stock Table
Field name	Data type	Constraints	Description
Date	Date	Primary key	Date
Crush_qty	Numeric(18,0)	Not null	Number of ice blocks crushed
Storage_qty	Numeric(18,0)	Not null	Number of ice blocks stored

Bill Table
Field name	Data type	Constraints	Description
Bill_no	Varchar(10)	Primary Key	Bill Number
From_date	Date	Not null	Initial  Bill Date
To_date	Date	Not null	Final Bill Date
Tax	Numeric(18,0)	Not null	Tax for particular bill
Qty	Numeric(18,0)	Not null	Quantity of ice blocks
Price	Numeric(18,0)	Not null	Total amount
Customer_id	Varchar(10)	Foreign key	Id of the customer
Bill_date	Date	Not null	Date on which the bill is issued

Indus Customer table
Field name	Data type	Constraints	Description
Cust_id	Varchar(10)	Primary key	Id of the customer
Cust_name	Varchar(10)	Not null	Name of the customer
Cust_address	Varchar(50)	Not null	Address of the customer
Phone	Numeric(18,0)	Not null	Phone Number of the customer

Salary table
Field name	Data type	Constraints	Description
Sal_Date	Date	Not null	Date on which salary is issued
Commission	Numeric(18,0)	Not null	Commission of the customer
Emp_id	Varchar(10)	Foreign key	Id of the customer
Month	Varchar(10)	Not null	Month on which the salary is issued
Year	Numeric(18,0)	Not null	Year

Employee Table
Field name	Data type	Constraints	Description
Emp_Id	Varchar(10)	Primary Key	Id of the customer
Emp_Name	Varchar(50)	Not  null	Name of the Employee
Emp_Address	Varchar(50)	Not null	Address of the Employee
Phone	Numeric(18, 0)	Not null	Phone Number of the Employee
Basic_Salary	Numeric(18, 0)	Not null	Basic Salary of the Employee

Price details Table
Field name	Data type	Constraints	Description
Price	Numeric(18,0)	Not null	Price of the single ice blocks
Year	Numeric(18,0)	Not null	Year

---

Accounting and sales system ER Diagram

---

---

Download Complete Documentation