Rental Management DB Schema

The polymorphic relationship seems okay upon first glance, although, I'm not sure you even need the type to track Income vs. Expense.

Why not use positive values to represent income and negative values to represent expense? That would make the computations a lot simpler, too.

@cbj4074 Thanks for the reply. The main thing I am worried about is that "what is a common practice in this kind of situation?". Although I can think of a lot of apps that need to care about the accounting side, there is not that many tutorials or articles that cover that. The main task of the app is collecting rent payments (not online) and recording it in the database. But other Income/Expenses needs to be recorded as well. We can then generate a report that states the actual net income for the owner of the house. You mentioned negative values for expenses but it will not change a lot because when I want to calculate maintenance cost alone, that is just a sum of amount I have spent on maintenance (needs to be positive in that case) but If I want to include it in the owner's Income Statement, then I need to have it as a debited value.

I still don't have a good picture in my mind of how everything can be managed through the Invoice->Transaction idea.

Any thoughts?

My inclination is that you should in fact sum the negative values when calculating the cost of maintenance. Anything that is an expense should be stated as a negative value, in my opinion, even when displayed on a report. If for whatever reason you want to display it as a positive value, then just multiply it by -1 for display purposes, on a per-report basis, as needed.

In any case, positive-vs-negative values is not a particularly important consideration, given the use-case you describe, and you could certainly store the type (Income or Expense), but taking that approach will complicate your queries unnecessarily, in my opinion.

My advice is to start building this. You can stare at the schema all day, but until you scaffold-out some models, pencil in the relationships, and play with it in Tinker, it'll be difficult to see any shortcomings in your logic.

I like to start with account types:

and chart of accounts

of course utilities can be separated.

then an expense or income can be entered:

I can do reports:

Something like this in laravel:

code for report

public function monthlyReport()
    {
        $bdate = Request::input('begindate');
        $edate = Request::input('enddate');

        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = `accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = `transactions`.`AccountID`))) where (`transactions`.`TransactionDate` Between '$bdate' and  '$edate') group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName`";
        
        $sth = DB::getPdo()->prepare($sql);
        $sth->execute();
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);
        $title = 'Monthly Report';
        return view('account.report', compact('quy', 'title'));
    }

Did not worry about binding dates, but for binding, and do bind, looks like:

where (`transactions`.`TransactionDate` Between :bdate and  :edate)

and

$params = ['bdate' => $bdate, 'edate' => $edate];
$sth->execute($params);

Normally date and numeric are safe anyway.

An example db facade:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Of course there are detailed reports as well, but usually a monthly summary is all board of directors need in this case.

And a "flag" for as example not paid rent you could do a monthly report on. Usually done by date in this case. So a calc of "X number of days past due" could be the flag.

Of course getPdo is just what I like, you can also design eloquent queries:

Just example here:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Just me, but I like the orm for maybe a one to many, but usually for larger queries, I prefer normal sql and pdo.

And usually in accounting specifically bookkeeping, you will need aggregates in many queries involving reports and summary data.

@jlrdw Thanks you for your reply and time. I think I have an idea now. So instead of having the income/expense in the Invoice table, you have that as a column for each transaction. Do you still have a table that is acting like the invoice table? My use for that will be anything that needs to be paid for example the rents will be added to the invoice table first and then if the transaction covers the full amount of the invoice, I set the invoice status to paid and otherwise it will be set to partial (so the status will be one of pending/partial/paid/pastDue). So where is that kind of functionality in your design?

The other thing is what do you mean by "of course utilities can be separated"? do you mean that I just enter utilities in the table that connects to the account type table instead of listing all kind of utilities or I make it as utilities and then a table for sub-type to connect to that accountname. For example utilities will be in that table and then the sub_account_type will have water, electricity, telephone, etc. all linked to the utilities.

We grouped utilities together what I meant is just have them in the same table but phone electric separate.

As far as an invoice goes are these properties going to be hand receipts also or just computer-generated receipts.

We gave everyone a hand receipt and that's what was entered into the general ledger as shown above.

If I was doing computer-generated it would just come from the general ledger and a blade view would be for print out but data stored in the database.

And are you dealing with invoices and receipts differently are using the terms interchangeably that's another thing to look at.

The above is mainly what the main accountant and the IRS needs.

And me I just try to keep things simple as possible and not overly relate things.

For example there were spays and neuters also, that was a separate table with relations.

In other words one customer with that invoice number would be the parent and the spays and neuters would be child records.

So monthly the income from spays neuters would be transferred to the above general ledger in other words.

All monthly reports and all data for the IRS and accountant are all derived from the main Ledger.

I have dealt with bookkeeping programming both at a Humane Society and a major trucking company and never had to have more than a one-to-many relationship.

Of course you could treat rentals as a separate table with its children and enter monthly into a main Ledger there are number ways to do what you trying to do.

In my example sometimes a summary report monthly was good enough. But sometimes a member of the board might say can I have a report this month of all the spays and neuters that took place.

So of course I had reports that can be printed for that table and its relations as well.

But remember the IRS does not need each detail record the IRS just needs the summaries in other words we made 30,000 this year in space and neuters.

However all detail records still need to be available in case there is ever an audit.

@jlrdw so basically you do bookkeeping after that transaction/payment is done. What I meant by invoice is an amount that the house owner needs to get from tenant. Now because the owner is able to also pay maintenance, then I will be using invoice for that too (although that is called bill technically). I think the difference of my system is that it is generating invoices automatically (every month the cron job will generate all the rents that needs to be paid for that month) so that the house owner knows who he has to chase for his money. Once he gets his money, then he can update the invoice as paid which will generate a transaction against the invoice so set it as paid or partial. The maintenance is also the same, you can assign maintenance job to a vendor and once the job is done you can pay them.

Have you done something like this? Or maybe you have better idea than mine (which still I think I am missing something)

Thanks for the reply.

No matter how it's done as long as at some point to close out a month everything is entered into one main Ledger which is where all the main reports meaning the summaries are derived from.

Normally I'd wait one full week in a new month to close out the previous month.

And a side note the IRS doesn't care what month you show the income, let's say Bob pays in June but was actually owed in May if it came in after May was closed out I would just show it in June and put in a notes field it was from may.

I actually learned this stuff from a CPA because I also did bookkeeping for the Humane Society for four years, so I made sure I knew exactly what I needed for the IRS at year end.

You could set up your system to automatically transfer figures to the main Ledger once a month is closed out.

For example you have maintenance and you have rents those could be Separate Tables of course with child records the goal is to have those monthly figures transferred to the main Ledger after a month is closed out.

Also get a system that works and stick to it do not deviate stick to those monthly dates.

@jlrdw Just one more thing :)

The utilities sometimes are on the owner and sometimes on the tenant. When the tenant pays directly to the service provider, we don't have to do anything about it. So then we have 2 situations:

1. Owner agrees to not charge the tenant for the utilities and pay to service provider
2. Owner agrees to get the utilities from the tenant and pay to service provider

In case 1 happens, the utility is an expense In case 2 happens, the payment that the tenant does to owner is income and the one paid to service provider is expense.

Does that mean that I have to add two of each (water as expense and water as income, etc.)?

Thanks!

In that case yes a utility will count as both income and expense. That is if the landlord is charging a small fee for the convenience.

If the landlord pays the exact amount to the utility company as they charge the tenant, this would probably be zeroed out.

Definitely do more research on that.

The only example I have currently is a non-profit taking in PayPal donations.

Let's say they took in $30,000 in donations.

Let's say the fees on that was $219.

The proper way to handle that is actually show the entire 30,000 on an income statement but turn around and show the 219 on an expense statement.

But as far as a landlord collecting the utilities and of course having to pay the utility company I would Google the heck out of it, myself I would even ask a CPA I would want to get everything correct.

Edit, after rethinking it definitely check with a CPA or known good bookkeeper don't even guess on this.

And I would not listen to anybody on any forum unless it came from a CPA.