What is the most efficient database schema for an accounting application?
Posted on 2013-05-31
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.