Housing Society management system database structure

I am designing billing structure for housing society yesterday I googled and gone through banking billing structure and designed my database structure but I am not sure whether this would be correct. So I am putting my billing database structure.

Please tell me if I am wrong any where or any changes has to be done in my database structure.

And one more questions where I have to post society balance (debit, credit )like eg expense Bldg insurance and income like Adv board hoarding and also not sure about balance audit trail (having it in transaction table of separate table with transaction id as FK).

Please note all table will have by default have created,modified time, by and ip address

Table billingstatement

id   | description                        |  amount  | Bill Month  | userId   | societyId 
 1   | Maint Chrg 1000 sqft x 5 per sqft  |  5000    | Aug-16      | 1001     |  101      
 2   | Water Charges                      |   200    | Aug-16      | 1001     |  101       
 3   | Construction Charges               |   300    | Aug-16      | 1001     |  101
 4   | Reserved Parking chrgs             |   500    | Aug-16      | 1001     |  101

Table Accounts

id   | balance(current bal) |  societyId  | modifiedTime         |
 1   | -6000                |   101       | 2016-01-01 21:01:01  |
 2   | -5000                |   101       | 2016-01-01 21:01:01  |
 3   | 1000                 |   101       | 2016-01-01 21:01:01  | 

Table transaction

id  |  amount |  balance | trans_type | trans_time          | account_id | 
 1  |  6000   |   0      |    1       | 2016-01-01 21:01:01 | 1          | 
 2  |  5500   | -6000    |    1       | 2016-02-01 21:01:01 | 2          | 

tran_type :- 1 = Payment by user, 2 = Income to society, 3 = Expense to society

Table map_account_user

map_id | account_id | user_id
 1     |  2         | 1001

If account mapping is not present then it means it is a society account and not a user account.

Reference :-

billing banking desing

banking project sample

Answer

I’m assuming you are designing a relational database. In a relational database, you normalize the data.

I’m having trouble following your database design because you have too many different fields called id. Each id field should get a unique name, so people can tell what the different id fields represent.

Let’s start with the Transaction table. Generally table names are singular. I capitalize table names and column names. You don’t have to follow that convention.

Transaction
-----------
Transaction ID
Transaction Type
User ID
Society Account
Transaction Amount
Transaction Time Stamp
...

Transaction ID is an auto-incrementing integer. It is also the primary (clustering) key to the Transaction table. Transaction Type is 1 = Payment by user, 2 = Income to society, 3 = Expense to society. I’m not sure what the difference is between Transaction Type 1 and Transaction Type 2.

Either the User ID or the Society Account column is filled in. The User ID column is filled in for Transaction Type 1 and the Society Account column is filled in for Transaction Types 2 and 3. The not filled in column is set to null.

Transaction Amount is always a positive value. Your code will subtract the Transaction Amount from the Society Account for Transaction Type 3.

You will create a unique index for (User ID, Transaction Time Stamp descending, Transaction ID) and a unique index for (Society Account, Transaction Time Stamp descending, Transaction ID). This allows you to quickly get all the transactions for a user or society account, for a given month.

Next, let’s look at the UserAccountBalance table.

UserAccountBalance
------------------
User ID
Balance Year and Month
Balance Amount
...

The primary key to this table is (User ID, Balance Year and Month descending). You maintain the historical balances for each month for each User ID. This allows an auditor to verify the balances by running queries against the Transaction table.

Next, let’s look at the SocietyAccountBalance table.

SocietyAccountBalance
---------------------
Society Account
Balance Year and Month
Balance Amount
...

This table is similar to the UserAccountBalance table, but for Society accounts.

Next, let’s look at the Billing table

Billing
-------
User ID
Billing Year and Month
Billing Type
Square Feet
Charge per Square Foot
Total Charge
...    

The primary key is (User ID, Billing Year and Month descending, Billing Type). I’m assuming that you only get one billing charge per billing type per month.

Billing Type is 1 = Maintenance Charge, 2 = Water Charge, 3 = Construction Charge, 4 = Reserved Parking Charge. You can generate the text on the bill from the values in this table, so there’s no need to store the text in the database. The Square Feet and Charge per Square Foot columns are filled in for Billing Type 1, otherwise they are null.

You still have to match up the payments with the billings, but this should be enough to get you started on the right path.