[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

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

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
missionarymike
Asked:
missionarymike
  • 6
  • 4
1 Solution
 
Ray PaseurCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
missionarymikeAuthor Commented:
So if I eliminate the totals column and set up a data grid, how could I see the individual totals?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
Dave BaldwinFixer of ProblemsCommented:
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
 
missionarymikeAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
I think this will be simpler than you think.  Do you have a sample table yet to work with?
0
 
Dave BaldwinFixer of ProblemsCommented:
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
 
missionarymikeAuthor Commented:
That is what I am looking for.

What is the query for the grand total?
0
 
Dave BaldwinFixer of ProblemsCommented:
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
 
missionarymikeAuthor Commented:
Thank you.  That works great.
0
 
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.  Thanks for the points.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now