Property Management Application ER Diagram (ERD)

I am in the process of designing a property management application in my language (RTL) similar to Buildium (buildium.com). I got stuck in the Accounting portion of the ERD. I was able to create the Bank Account and Chart of Account (COA) ER. Now I am working on the Bill system. The billing system for items/service done on the property which includes the Date, Due Date, The Vendor, Property, Account (part of COA), Description and Amount.

My question is where to the add the bill and how can I connect it to the Property so this bill only belongs and appears in the that property financials. Additionally, If anyone has an Accounting system ER please share it with me. Here is my ERD:

Thank you

I am a bit iffy about the relationship between the COA and Bank Accounts. Is every COA a type of Bank Account? Or do you have Creditors and Debtors also? In fact a billing system exists because you’re offering some kind of credit facility and not operating on a cash only basis. A COA is kind of a polymorphic relationship (if thats the right term). A COA is one of the following types of accounts (bank, debtor, creditor, fixed assets, depreciation, taxation, etc …) Only once you have debtors and creditors can you have Invoices, invoice-line-items, etc.

I could draw it out in a diagram if you like but there is no one size fits all types of business anyway.

Sent from my XT316 using Tapatalk 2

Hello,

Thank you taking the time to help me out.

Actually, what I mean is that the COA can have one or more Bank Accounts. As far as as Billing, yes I will be having accrual type payments. Because tenants will be invoiced, etc.

Am I doing the COA and Bank in a wrong way?

Thx

I don’t know that its wrong. Its just that I’ve not seen a perfect way of modelling this “polymorphic” relationship in a relational database. You have a chart of accounts of different account types. For instance debtor, creditor, bank, revenue, expenditure, and so on. Some of those account types may have extra fields that none of the other account types have. For instance a bank account may have bank and branch fields and whether it savings, credit or chequing. You end up having the COA account like the hub of a star shape of whole raft of account sub types, each subtlely different. Its a real pain if at some point you need to perform database queries across account types.

There is another way of representing the data and that is to take all the unique account type columns and put them in an extra fields table where the column names might be Account_type, Field_name, Field_Value. You’re effectively putting tables inside a table. So a row of that table may be something like (‘Bank’, ‘BankName’, ‘Taranaki Credit union’). This reduces the number of tables and is more scalable but is not a perfect solution either. You lose the advantage of the automatic integrity checking that the database can perform on those extra columns (such as enforce not null). Unfortunately I don’t know of any silver bullet here. There is a tradeoff between tables that are difficult to query together against giving up database enforced data integrity. What I usually do is give up on the enforced database integrity but it really depends on how important that is to you.

Sent from my XT316 using Tapatalk 2