Solved

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

Posted on 2013-05-31
11
538 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 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Save on storage to protect fatherhood memories

You're the dad who has everything. This Father's Day, make sure your family memories are protected. My Passport Ultra has automatic backup and password protection to keep your cherished photos and videos safe. With up to 3TB, you have plenty of room to hold the adventures ahead.

Question has a verified solution.

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

Monitoring systems evolution, cloud technology benefits and cloud cost calculators business utility.
Most of the applications these days are on Cloud. Cloud is ubiquitous with many service providers in the market. Since it has many benefits such as cost reduction, software updates, remote access, disaster recovery and much more.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

910 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

24 Experts available now in Live!

Get 1:1 Help Now