What is the most efficient database schema for an accounting application?

Posted on 2013-05-31
Last Modified: 2013-05-31
Hello Experts.

I am in the process of creating a web application using PHP and MySQL which I have done many times, however this one is a little different as it has totals for each account.

A simple explanation of the database has 2 tables:

Table 1: Name -> Accounts, Fields -> id, name, type, total
Table 2: Name -> Register, Fields -> id, account_id, date, is_deposit, description, amount

Question:  I don't want to manually adjust the total for each account as the new register item is entered.  I thought maybe that a stored procedure or function might be ideal but haven't used them before.  What is the best schema to handle the totals for each account.

I would like to be able to run a SELECT query for each account, or all accounts and return the total for that account.

Should I have a stored procedure or function for each account, or have one procedure or function that is CALLED each time a new register item is entered, and the procedure or function would iterate through all the regiser items and calculate the account totals.

I don't want to overload the server with unnecessary work.

Thank you.
Question by:missionarymike
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
LVL 110

Expert Comment

by:Ray Paseur
ID: 39212019
You won't overload the server.  The kind of query you will use is SELECT SUM(colname).  If you have an index on colname, these queries will run very fast.  I probably would not put a "total" value into the Accounts table - carrying both the transaction log and the total would seem to proliferate data in a way that violates the DRY principle.  I would compute the totals for each account every time.
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39212030
I agree, the server is made to do work.  As for Stored Procedures, I would not use them until you have a system that works.  Problems with Stored Procedures can be like hidden code you can't easily troubleshoot.

Author Comment

ID: 39212045
So if I eliminate the totals column and set up a data grid, how could I see the individual totals?
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

LVL 83

Expert Comment

by:Dave Baldwin
ID: 39212060
Basically the same way you would get them for the 'totals' column except you do it only when the request is made.  That way it is always as current as the database.

What is the point of the application?  And are you going to try to do 'real accounting' by calendar periods or just keep a totals sum?

Author Comment

ID: 39212128
Initially it will be a simple check register where each register item will have an amount and a Column for is_deposit.  For the accounts, each will have a total for all of the register items related to it.  Then I will need to get a grand total from all the register items or all of the accounts.  

I would like to be able to have a data grid with all of the accounts and their totals.
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39212140
I think this will be simpler than you think.  Do you have a sample table yet to work with?
LVL 83

Accepted Solution

Dave Baldwin earned 500 total points
ID: 39212157
Here's the table.
id 	account_id 	ddate 	is_deposit 	description 	amount
1 	27 	2013-05-31 	Yes 	The first deposit 	23.05
2 	22 	2013-05-07 	Yes 	The earlier deposit 	11.11
3 	17 	2013-05-22 	Yes 	Account 17 deposit 	17.56
4 	27 	2013-06-04 	Yes 	The next one for #27 	11.19
5 	22 	2013-05-22 	Yes 	Some other 22 	22.22
6 	17 	2013-05-17 	Yes 	17 again 	1.99

Open in new window

Here's the query.
SELECT `account_id`, SUM(`amount`) FROM `register` WHERE `is_deposit` = 'Yes' GROUP BY `account_id`

Open in new window

Here's the results.
account_id 	SUM( `amount` )
17 	19.55
22 	33.33
27 	34.24

Open in new window


Author Comment

ID: 39212189
That is what I am looking for.

What is the query for the grand total?
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39212271
Don't know.  I would probably do that in PHP as I got each subtotal.  I don't think you can do that in that same query because it is returning rows for each group.

Author Closing Comment

ID: 39212303
Thank you.  That works great.
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39212366
You're welcome, glad to help.  Thanks for the points.

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Both in life and business – not all partnerships are created equal. Spend 30 short minutes with us to learn:   • Key questions to ask when considering a partnership to accelerate your business into the cloud • Pitfalls and mistakes other partners…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question