Solved

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

Posted on 2013-05-31
11
545 Views
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.
0
Comment
Question by:missionarymike
  • 6
  • 4
11 Comments
 
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.
0
 
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.
0
 

Author Comment

by:missionarymike
ID: 39212045
So if I eliminate the totals column and set up a data grid, how could I see the individual totals?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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?
0
 

Author Comment

by:missionarymike
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.
0
 
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?
0
 
LVL 83

Accepted Solution

by:
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

0
 

Author Comment

by:missionarymike
ID: 39212189
That is what I am looking for.

What is the query for the grand total?
0
 
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.
0
 

Author Closing Comment

by:missionarymike
ID: 39212303
Thank you.  That works great.
0
 
LVL 83

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Many CHPs use the buzzword ‘Cloud Hosting’ to sell the idea of reliability. Most consumers have the opinion that cloud hosting is easily scalable and can handle just about anything. Further, most CHPs are not transparent and hide the underlying arch…
Do you know what to look for when considering cloud computing? Should you hire someone or try to do it yourself? I'll be covering these questions and looking at the best options for you and your business.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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…

713 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