|
Would accounting software developers please comment on this question? What is the best table design for an integrated accounting system, given everything that you know today, PCs with 128Megs RAM, 10 GB Hard disks, etc.? Flatfile, or normallized, or Intuit? A very common design today, seems to be a collection of separate tables, one for each major transaction type or journal (General Journal, Sales Journal, Cash Receipts Journal, Purchases, etc.) Each table would have index key, date, and a few other common elements but beyond that, the fields would be different for each journal (e.g. Sales Journal would have Items, CRJ would have Cash Account etc.) Peachtree Accounting for Windows is an example of this table design. The fields and tables are laid out on Robert Walraven's Pages http://www.multiwareinc.com/ --Robert probably knows more about Peachtree's data files than the programmers left in Norcross. Another design, exemplified by Quickbooks, Quicken and GnuCash, has "tables" for parent accounts such as cash and receivable accounts, and another "table" with one or more balancing entries (the offsetting debit or credit of each transaction) associated with each entry. You can't post a journal entry containing more than one Accts Receivable or Accts Payable account. Scott Treseder has been deciphering their database since 1994, lays out the tables at http://www.datablox.com/qb/qbtran.htm Anybody who has tried to generate a Cash Flow statement from a database will suspect that Intuit was trying to come up with a system that is capable, somehow, of reporting on the disposition of funds... or maybe I'm being charitable, maybe they just inherited their table design from early Quicken designs that were basically only a checkbook. I've imported data from both QB and PT products. There is an early version of my Peachtree data import utility at http://www.gldialtone.com/PeachUtl.htm --basically, to navigate and search thru all the data, as a CPA it has always been more convenient for me to "denormalize" the data by importing all the journals to a single table browse. In the above VB/Jet application I can instantly resort on any column heading, set filters, etc. It is great for surfing the data set. I've become more convinced the best table design is a flat file containing a row for every Debit and Credit of *every* journal in the same table. Systems Union SunAccounts and some other very successful high-end ledgers continue to use this design. (There are of course, separate tables for Chart of Accounts, customers etc.) I know this sounds nuts, having separate lines in the flatfile for every line of sales on every invoice, for example. But programmers can build components for every purpose, manipulating accounting data for various journals or reporting, on a flat file. Once they have objects that work for one journal, it works for all of them. The absolutely most important priority in accounting software design is achieving something that can be rapidly maintained and morphed into new platforms and architectures. The 2-5 year delay for "your software provider" to get caught up with the market is TOO LONG. It hurts the owner of the business when their software can't support online selling, for example, or run off the customer list maintained in ODBC databases or MS Exchange. It is also easier for the *millions* of users to build and save reports from these flat views, like you see in Peachtree and Quickbooks, than from "normalized" data where you have to associate many fields. This is just magical when you want to mine the data or load it into an OLAP engine. And a trial balance can be built with a single Group-By query. Multicurrency reports and Multicompany consolidations likewise can be done with surprisingly easy set-oriented SQL commands. Databases nowadays waste no disk space when a field is blank. (For example, the Item ID might be blank for rows that are General Journal entries. In older databases with fixed length fields, there would be empty space on disk.) Larger number of fields can be indexed by todays larger memory addressing space, and searched and processed faster by faster CPUs. Very few small businesses run GLs larger than a few megabytes. In other words, the entire data set fits in RAM many times over. Thus, the architecture should be reoptimized to make it easier for programmers to keep up with rapidly changing requirements, and easier for the owners of the data to use it with 3rd party report writers, etc. * Todd F. Boyle CPA http://www.gldialtone.com/ * International Accounting Services LLC tboyle@rosehill.net * 9745-128th Av NE, Kirkland WA 98033 (425) 827-3107 Platform changes are RADICAL and accounting software is never going to survive the transition to Webserver based accounting. The Business Owner should buy accounting software that maintains simple, orthogonal data sets. This is your best insurance against being stranded in a dead-end product. No human being can migrate the Quickbooks data sets. Because the code is too complex for Intuit to rewrite it in Java or ActiveX for the Web. And in 5 years the web will be DUST and you'll be required to migrate your accounting data AGAIN to something else. Get it? |