Solved

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

Posted on 2013-05-31
11
535 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 108

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 82

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
 
LVL 82

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 82

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 82

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 82

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 82

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
The viewer will learn how to dynamically set the form action using jQuery.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now