I am designing an accounting program. Why? Because I'm crazy! Actually, I am doing it because I find most accounting programs very restrictive and often very difficult to get information from.
My first design problem is how to store the many transactions. More specifically, how many tables would be optimum for storing the vast number, and variety, of transaction in a database of this type. I have come up with basically four possible methods and would like your opinions on them. The methods are listed below along with what I think are good and bad about each one.
1) Store all transactions in a single table. - This method is simple and would work for accounting systems with very few transactions, but I think that it would very quickly cause performance problems in the system. Because it is monolithic in its design, I think it would be slow.
2) Each account has it's own table. - While this method is orderly, the number of tables could become ridiculously large. Not to mention queries.
3) Store transactions by type of account. - For example, all balance sheets accounts would be in one table and all income statement accounts in another. In many ways this is like Method 1. It's somewhat monolithic in its design which I'm sure will cause problems later on. Also, the income statement table would become very large as most institutions have many expense accounts.
4) Store transactions by type. - For example, all revenue transactions would be in one table, all receivable transactions in another table, etc. This method seems to group the transactions in an orderly way while of tables and the number of transactions in each table manageable.
Currently, I am leaning toward some solution that resembles Method 4. I would appreciate you opinions and critisims on this matter. Thank you!
I would say that methods 1 & 4 were the only viable ones, and method 1 may be preferable.
Method 2 is BAD! Any design that requires creating a new table every time there is a new customer is WRONG.
Method 3 would have transactions of the same type in different tables because they belonged to accounts of different types. This doesn't seem right either.
Method 1 is the most flexible, because it allows you to easily perform queries that cross account types and transaction types, rather than having to UNION together many queries to do so.
As you say your database will have a vast number of transactions, presumably you will be using a suitably scalable DBMS. This should not have any problems coping with a transaction table with 10s or 100s of millions of records - provided it is suitably indexed.
Option one is probably your best bet. Most of the accounting packages I have worked on use one table for each year's activity. So allow for the table name to change each year. This annual rollover keeps the table size manageable.
The support/validitiy checking tables are kept separate since they don't change much from year to year.
Bruce: How do you carry the Balace Sheet transactions forward, in detail or in summary?
Tony & Bruce: Here is another idea. Have, say your sales journal, tied to a table which stores all the sales transactions. These transactions are posted in summary to a General Ledger table. The major drawback here, I believe, is ensuring that the data is consistent between both tables. The benefit would be that it would make creating the Financial Statements much easier, and faster.
I'd carry the balances over at the detail level. There's always a "this month/quarter/year-last month/quarter/year" report hiding in the bushes. Having detail transactions is slower, but allows you more flexibility in reporting.
Tying in the Sales transactions does complicate things a bit.
Whatever approach you use for the detail accounting transaction history...should also be used on the Sales transaction history. This will make it easier for you to find the associated data.
Check out the financial reports and inquiry screens currently in use by the company. They may give you a better picture of how the data is really being used.
Typically - CEO/CFO wants executive summary and trends, Managers want this period/last period and budget, and clerical staff wants current month and exceptions.