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 |
| 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
Leave a reply