SQL Table and row linking - Double entry accounts

**andybonse2012** · Aug 13th, 2012, 10:59 AM

Hello,

I am making my program, however I am unsure on how to create the double entry accounts side of things.

Here is the design:

Tenant transactions
Landlord transactions
main cashbook

Now when I collect the rent it goes into tenant transactions and main cashbook, but inside these tables are the rents collected and fees etc for all the tenants! and in cashbook mixed landlords and tenants.

I want to make it so once its posted into tenant and casbook transactions to be able to delete it from just one the tables, either. And it deletes from both not just one, otherwise you would have messed up accounts.

Please, please help me get around this I cant think of any ideas, ids wont work as they might be different or already used in another table, help!!!

lol

**dunfiddlin** · Aug 13th, 2012, 11:41 AM

Well the first thing I would say is that if I were a tenant I would be extremely unhappy with the way things are done. I should expect to have a table (ie. a rent book) to myself and for transactions to be recorded both there and in the main accounts. Secondly I would expect the very idea of 'deleting' any transaction to be anathema!

Secondly, if you want help coding something, please show us what you have done rather than providing us with airy-fairy concepts. We like to deal in the concrete round here.

**Evil_Giraffe** · Aug 13th, 2012, 12:05 PM

Just a quick question - what do you expect to get from double entry accounting? Because the way you've described it, I'm not sure you've gained anything. The point of double entry is that you have two separate entries in separate systems, such that incorrect entries (whether that be by fraud or simple mechanical error) in a single system can be detected.
It seems to me that you can get the same functionality from a single table with a transaction appearing once, and there is a column for the debited account and one for the credited account. Then to display each account you simply select the transactions where the debited account is equal to your account or the credited account is equal to your account.

If you actually want to do double entry you will need to have two separate systems with two separate databases that have different DBAs that exist on two separate servers that do not have the same server/network admins (which probably means two separate network domains). Are you sure you want to do double entry accounting?

**techgnome** · Aug 13th, 2012, 12:20 PM

"If you actually want to do double entry you will need to have two separate systems with two separate databases that have different DBAs that exist on two separate servers that do not have the same server/network admins (which probably means two separate network domains). Are you sure you want to do double entry accounting?" -- double entry accounting doesn't mean two different systems... it just means that you have a way of tracking where something came from and where it went... every transaction is entered twice...usually in Debit/Credit pairs... (at a minimum they done as pairs) ... as oppose to a single entry journaling, where a transaction is a single entry. Think of a checkbook register... that's a single entry system. You write a check, and record it in the journal. It's just one entry...

"I should expect to have a table (ie. a rent book) to myself " ... yeah, one would think so... but as someone that's built A/R systems over the years.... doesn't work out that way.

As for creating a double entry system... first thing you need to do is to stop thinking like a developer... that's the first mistake often made when creating accounting systems... it ends up being designed and created by developers that have no sense of how accounting works... the next thing to do is to get a book, Accounting for Dummies if you have to... beat yourself over the head until you are sure you're no longer thinking like a developer... then read the book. Then when you have an idea of the basics... use pen and paper to mimic what you are trying to do... smack yourself everytime you think about a table or something programmy.... that's jsut going to get you into trouble. THEN once you have written it all out... THEN you start start thinking like a programmer and breaking things down... but keep that internal accountant handy. And then go make friends with an accountant or CPA that's willing to let you pick their brain on how it should work.

I can already tell you that your table design... wrong approach...

you should have a table of Journal Items, as subtable of Jounral Item Details, and one more table that tracks accounts. Each account gets a code. When you then record a transaction, you record A PAIR of transactions...
First you DEBIT an account, and then CREDIT the next.... the codes recorded with the transactions tell you where the money moved from/to ... if you sum all the debits and subtract the sum of all debits, you should get 0.

OK...

So, let's use your example:
Tennat rent is due....
in the Accounts table you have the following:

Code:

CODE     Account Name           Description
0001      Rent - Apt 01            Rent for Apt 01
0002      Rent - Apt 02            Rent for Apt 02
9999      Cash                        Cash account
9988      Deposits                   Bank Deposits
9900      Receivables               Receivable amount

So let's say the rent on Apt 01 is $500/month .... rent is now due for that tennant....
So the first thing that needs to be done is to put that tennant into debit and note his rent as a receivable.
So a Journal Item is created:

Code:

ID Date           Description
1  8/1/2012 -- Rent due for Apt 01

Now in the Journal Item Details, you record the Debit from the tennat's account, and the crediting of the Receivables:

Code:

ID   JOURNALID AccountCode  TransactionType Amount
1    1              0001              Debit                500
2    1              9900              Credit               500

Now we show that the tennant owes us 500 for the rent.... when they make a payment....it's a new Journal Item

Code:

ID Date           Description
2  8/5/2012     Rent paid for Apt 01

and in the Journal Item Details, we 1) debit the Cash account, 2) credit the tennant's account, 3) debit the receivables, and 4) credit the deposit (presumably the rent will be deposited into the bank).

Code:

ID   JOURNALID AccountCode  TransactionType Amount
3    2              9999              Debit                500
4    2              0001              Credit               500
5    2              9900              Debit                500
6    2              9988              Credit               500

Head explode yet? This stuff isn't for the weak hearted or timid. It's serious stuff. And I didn't even get into reversals, adjustments or anything like that. This was just a simple example...

On the other hand... this is all predicated on wanting to do it right. I don't know what your intent is, but trust me... if you're building a foundation for something long term... do it right... trust me. I'm battling a system what was built by developers with no accountant help or any inkling of how accounting works... and it isn't pretty. We recently had someone with an accounting background re-build our entire GL Accounting system... he turned what was close to 30 tables.... into something simple, not all that far different from the designed I just used in my example. Had it happened two years ago.. or right from the start... I wouldn't be worried about ulcers right now... I'be happily plugging away for a new client.

Anyways, I realize some of this is deep, but at the very least it's worth reading, even if it doesn't stick. It's possible, that this maybe overkill for your needs and you'll just end up ignoring it. That's fine. At least the next time I see some one going down the wrong accounting road, I'll someplace to point them to.

-tg

**andybonse2012** · Aug 13th, 2012, 02:28 PM

Thank you for your replies.

I have misunderstood double entry accounts it seems, I used to work for a company who sold lettings software etc, it was no double entry and if you deleted a transaction from say the cashbook it didnt from the tenant transaction log, so the accounts got messed up and clients were not happy as you can imagine!

My idea is to make software that does not have the same mistakes and more user friendly functionality. So Ill start again.

Oh and to the first reply, I would give each tenant a separate one, but how can I have tables for each individual tenant and property, the database would be huge and untidy.

I planned to have a table purely for tenants transactions using a unique tenant id, the same for landlords, agents and the maincash book.

My confusion is deletions,

I can easily use insert statements to put the rows into each seperate table using a button, however they are not linked and if then I added a deletion button, it would delete the seperate row in the individual table.

I want one button to delete a linked transaction, so If I deleted it from cashbook, OR tenant transactions, it would remove the row from the remaining entity.

I hope this makes more sense.

I appreciate your replies thanks.

**MattP** · Aug 13th, 2012, 02:39 PM

http://msdn.microsoft.com/en-us/library/ms973833.aspx

Some business rules require that a group of modifications be treated as a single transaction. For example, in accounting software, a debit and a credit must succeed or fail together because the accounts must "balance." If a debit succeeds and the credit fails, the accounts will not balance.

**dunfiddlin** · Aug 13th, 2012, 04:18 PM

the database would be huge and untidy

Unless you have several million clients it will certainly not be huge and it was exactly for the purpose of keeping these kinds of records that relational databases were invented in the first place. Untidy is having all the different tenants thrown in together without any obvious way of tracking their payment records. When you're handling people's payments it makes sense to have corroborating records even if they are otherwise redundant. And as I said, there should never be any need to delete an entry of any kind. Even if a mistake is made it should be corrected by a further entry never wiped away.

**techgnome** · Aug 13th, 2012, 06:03 PM

Uhhhh.... yeah no. If you're putting tennant payments into different tables as your sole means of keeping them seperated.... something is wrong. If you're building a library systembwoild you put mystery in one table and scifi in another and historical fiction in yet another table? No... you would have a categories table... and then the book table would fkey to it. Your solution sounds EXACTLY what the past developers did to our system... and *I* am the one paying for it. There are now a dozen tables, all which look like each other all storing the same info... the only difference is one stores revenue one stores property another etoresbstock another benefits.... and so on. Then try reporting on it... trust me, its bs.

-tg

**dunfiddlin** · Aug 13th, 2012, 06:17 PM

As you will but I'm bound to point out that books are not making regular payments to anyone and expecting you to know exactly when and how the money was received! And I'm afraid property, revenue, stock and benefits are entirely different things and it's quite appropriate to have them separately recorded. I would seriously consider consulting with the company accountant and a tax expert before you commit yourself to a single entry system if this is going to be your main or sole record.

**techgnome** · Aug 13th, 2012, 07:36 PM

I'm afraid we'll have to agree to disagree... and I never advocated a single entry system... if you look back on my example, you'll see that it is in fact a double entry accounting system... I have worked with accountants before... that's how I came to help build A/R systems... while they may be physically different... all of those to an accountant, are all the same... they are financial transactions... OK... so you want to know all of the payments made by the tenant in Apt 01? Query it out... SELECT * FROM JournalItem where TenantID = 1 ... what's so hard about that? Yes, a book isn't going to make payments, but it does get checked in and out... a book is a book, a tenant is a tenant.

Ok, let's look at it this way.... as the Management of the apartment complex.... I want to see ALL payments .... if it's all in the JournalItem table.... SELECT sum(Amount) from JournalItem .... baddabing.... done... otherwise I have to go to each table and sum it up, then sum up those results. Heck! What if a tenant moves out? Now their table is invalid! Now I have to figure out when to include a table or exclude it...

But hey... what ever... I don't have to maintain it or use it so what do I care?

-tg

**Evil_Giraffe** · Aug 14th, 2012, 04:41 AM

Originally Posted by techgnome

"If you actually want to do double entry you will need to have two separate systems with two separate databases that have different DBAs that exist on two separate servers that do not have the same server/network admins (which probably means two separate network domains). Are you sure you want to do double entry accounting?" -- double entry accounting doesn't mean two different systems... it just means that you have a way of tracking where something came from and where it went... every transaction is entered twice...usually in Debit/Credit pairs... (at a minimum they done as pairs) ... as oppose to a single entry journaling, where a transaction is a single entry. Think of a checkbook register... that's a single entry system. You write a check, and record it in the journal. It's just one entry...

Okay, looks like I got myself confused. I thought the separate recording was an integral part of the DE system, but I was clearly mistaken. So what I described above apparently is still essentially a Double Entry system by my revised understanding because the transaction is linked to two accounts. I suggested recording the transaction with a single line, because I'm not really sure what the difference is in terms of data storage between:

Code:

ID   JOURNALID AccountCode  TransactionType Amount
1    1         0001         Debit           500
2    1         9900         Credit          500

and

Code:

ID   JOURNALID DebitAccount CreditAccount Amount
1    1         0001         9900          500

When pen and paper was used, the transactions were recorded twice to detect recording errors. Nowadays we can rely on databases to store these things correctly. Both of those lines in the first example will be recorded in a single transaction, right? So they will either succeed or fail together. There are no transcription errors where one record will be recorded and the other not. Similarly, the 'amount' value will be calculated once and put in the SQL INSERT statements without any risk, so we don't really gain anything from being able to check that the amount values match.

What I hadn't accounted for in my thinking was the next example you give where there are four entries for one journal item - but this surely is two transactions?

This is just my understanding of the intent of the system - I've picked up bits from my delving into single-entry systems (I have a slow-burning cashbook type app I'm (not really) working on in the background). I really would be interested to hear if there's something I'm actually missing. Clearly, the accountants would want to have this data presented with two separate entries on screen, but that doesn't necessarily mean it must be persisted like that, no? Although, having said that I don't see anything inherently harmful about storing it with two records (database transactions allowing us to know that they'll be recorded correctly as per my argument for the opposite above; storage costs being negligible; can't imaging the additional IO will be horrific given the data almost certainly will be written to disk right next to each other) so the additional harmony between the persisted form and the user mental model is probably worth it.

**andybonse2012** · Aug 14th, 2012, 05:33 AM

Hi Guys,

I've worked it out properly in my head now after a lot of thinking my head literally hurt and I wanted to go crazy and smash things up because it seemed too complicated, however after thinking logically;

Conclusion;

1 table purely for transactions,

The table will have transactions for landlords, tenants, contractors and the main cashbook.

I will seperate each entity by a uniqueID for each transaction, the cusID(Imported from the landlord,tenant or contractor tables) each tenant landlord and contractor has a primary key so each table i.e tenant table has a unique row reference will will be the CusID in transaction table.

There will be duplicate CudID's in transaction table, but thats perfect as I will then have colums IsTenant, IsLandlord etc.. So

1) Primarykey £300.00 33(cusid) 4531(uniquetransid) 1(istenant) 0(islandlord) 0(iscontractor)

So if I wanted to have a tenant record open on the transaction tabs, I could use something like select * from transactions where cusid = 33 and istenant = 1

As 33 in the tenant table is unique and is tenant = 1.

Same applies for landlords and also deleting transactions delete from transactions where cudid = 33 and transactionid = 232 and istenant = 1

Does this make sense?

**Evil_Giraffe** · Aug 14th, 2012, 05:51 AM