Db structure

[Derek Atkins]

Phil,

Phil Longstaff <plongstaff at rogers.com> writes:

> For the most part, I've tried to make the db schema match the XML schema, 
> including warts.  There are 2 places I'd like to change, and am looking for 
> feedback:
>
> 1) Each account stores a number of boolean flags (tax-related, hidden, 
> placeholder) in the slots table.  It would be simpler if these were just 
> boolean values in the accounts table.
>
> 2) Each budget stores all of the budget information in slots, with path names 
> of <guid>/<period> (e.g. 294ddec82b0840980d98203/12).  These should be moved 
> to a new budget_values table with columns id (autoinc/primary key), 
> account_guid, period_num, budget_value (numeric).  This would allow better 
> access to the budget info by external tools.
>
> Both of these proposals would involve moving the db storage for certain fields 
>>From slots to a more normal table structure.  In the case of the account flags, 
> the xml file structure could also change to add "<hidden>true</hidden>" format.

I see two ways of doing this.  One way is to change the internal
and external structures in order to handle this.  It's a a lot of
programming to do the old-format to new format conversion (and test
that we can continue reading both formats).  It also makes the files
no longer forward-compatible (I think -- I always confuse f-c and b-c).

The other way is to keep the structures as-is, don't modify the XML,
but in the objects save/restore functions you could translate the
KVP info into row data (and 'remove it' from the KVP) on save, and
on load you could read the data from the DB and insert it into the KVP.
I think it's probably just as much work to do this but we don't lose
the file compatibility.

Of course there are good arguments for both approaches.

> This leads to potential backwards compatibility problems in the xml file 
> format.  However, as long as 2.4 can read 2.2 XML files, do we need to keep 
> backwards compatibility so that 2.2 can read 2.4 files?

Historically we have made non-compatible changes to the data file.  For
example we did the SX incompatibilities in 2.something.  Ideally
we plan for these changes so that you can make x.y.<late> be able
to read the files from x.y+2 -- but that doesn't always work out.

Just my $0.02.

> Phil

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available