I am designing a billing structure for a housing society.
What changes have to be done?
Where do I post society balance (debit, credit), e.g. Bldg insurance expense , and income e.g. Adv board hoarding?
How do I handle balance audit trail (having it in transaction table of separate table with transaction id as FK)?
All tables by default have created and modified time, creator and IP address.
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
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 |
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)
map_account_user
map_id | account_id | user_id
1 | 2 | 1001
If account mapping is not present then it is a society account and not a user account.
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.