Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-05-31
11
Medium Priority
?
556 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 111

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 84

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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 84

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 84

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 84

Accepted Solution

by:
Dave Baldwin earned 2000 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 84

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 84

Expert Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There’s a movement in Information Technology (IT), and while it’s hard to define, it is gaining momentum. Some call it “stream-lined IT;” others call it “thin-model IT.”
The article lists top benefits which gaming industry bestows with the assistance of cloud computing technology.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

824 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