DB schema change plans, was re: Call for testing experimental patch

["Chris Travers" <..hidden..>]

Just to clarify and provide some acknowledgement that we are probably
largely on the same page here (believe it or not).

the problem with "just stop coding and fix the db schema" is that most
of the new features aren't really new and are a whole lot easier to
add now than later even with the work of changing the db schema.  Also
we are putting a *lot* of effort into fixing the db schema as well but
in many cases, db schema changes depend on coding changes because the
entire application is built using techniques that are *very*
unfriendly to data integrity controls.  For example, Dieter's standard
way of doing an "upsert" is:

insert int table (field) values (unix_epoch)

then update table set ... where field = unix_epoch

Obviously this makes it difficult to add NOT NULL constraints because
the code does not support them, so it is going to be *slow* going in
these areas.

Our current approach seems to be:
1)  Fix all issues involving the acc_trans table first.  This is the
main financial log and needs to be the top priority.  We are getting
close, but 1.2 should be the final target since we want to put lots of
things through more testing (after 1.1, the only thing left is a
foreign key constraint on trans_id but I want to test this more).

2)  Merge duplicate tables (ar/ap, customer/vendor, etc).  This may
happen by 1.2.

3)  Work our way in by removing unnecessary tables from the
id_tracker, separating entities wherever possible (why are projects
and warehouses considered to be transactions?).  Most of these are
issues of longer-term maintainability rather than data integrity
issues.  Each table or group of interdependant tables will be revised
by itself using accepted database design principles.  We have people
who are taking ownership of this issue.  But this is a long-range
project.

At the same time, we will be rewriting the API code to allow us to do
things like add NOT NULL constraints and the like.