DB design document

[Bill Gribble]

On Sat, Dec 16, 2000 at 12:29:50PM -0500, David Merrill wrote:
> Unfortunately, however, allowing *very* large text fields has serious
> drawbacks in databases that it does not have in XML. You pay a price
> in terms of storage space and indexing, which translates to query
> execution speed. Not just on the record with large entries, but on all
> of them.

We need to be more specific about what we need in terms of query
efficiency.  It may make sense to use one storage/indexing strategy
for non-text fields and a completely different one for text fields.
It's my opinion that having arbitrarily long text fields is a
prerequisite that other factors need to work around.  

What I haven't seen here is any questions about how gnucash uses its
data.  I may be mistaken, but I would think that how the data is
searched and processed would be at least as important for the database
design as the types of its fields. 

Also, I'm concerned that there hasn't been any discussion about how
the database backend interacts with the engine Query API.  I think
there's been consensus among the gnucash developers for a while that
when we go to a real database backend, we will need to implement some
kind of query caching system to allow for a single gnucash 'Query' to
be processed in multiple parts by the SQL backend (this is to prevent
the GUI from sucking huge amounts of data if it can only fit 20-50
transactions in a single window).  Presumably this will have some side
effects on the design of the database schema.

Bill Gribble