Subject: Database design for accounting systems
Date: 1999/04/28
Author: Todd Boyle <tboyle@rosehill.net>
    Posting History HOME
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?