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.
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.
Ray Paseur
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.
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.
ASKER
So if I eliminate the totals column and set up a data grid, how could I see the individual totals?
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?
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?
ASKER
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.
I would like to be able to have a data grid with all of the accounts and their totals.
I think this will be simpler than you think. Do you have a sample table yet to work with?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is what I am looking for.
What is the query for the grand total?
What is the query for the grand total?
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.
ASKER
Thank you. That works great.
You're welcome, glad to help. Thanks for the points.