Updated DDL for SQL backend

[Josh Sled]

On Fri, 2006-10-27 at 12:04 -0400, Derek Atkins wrote:
> Josh Sled <jsled at asynchronous.org> writes:
> 
> > There's a deeper modeling issue with SXes. We use a seperate, parallel
> > AccountGroup to store template transaction data, in which Accounts are
> > named as the string representation of the SXes GUID, and contain
> > Transactions with Splits which have their *real* Accounts, as well as
> > the credit and debit cell text formulae, in KVP frames.
> 
> I know David was working to re-model the AccountGroup...

True, though as I recall by giving Accounts self-hierarchy (rather than
with the current object + container system).  So long as that's true,
then the book can contain a reference to the SX Account object graph
along side the "normal" Account object graph.

Either way, it becomes more of an issue in the DB, though, since it'll
be easier to write an incorrect query like {select * from accounts where
type = 'asset'} that would pick up SX accounts that aren't really part
of the "normal" hierarchy.  As well, modifying the query to be correct
is actually a PITA, as you'd want to walk the tree to make how it's
rooted (either as the SX tree or the "normal" tree), and dbs generally
don't play nicely with hierarchical relationships. :/

I don't have a good solution, at present ... just bringing it up.
Probably the right thing is for SXes to just suck it up and model
template transactions seperately from the "real"
accounts/transactions/splits, though there's some serious downsides
there, too.


> > I don't see a reason to model this differently in the DB than in the
> > object model.  Though we might want to change the object model.
> >
> >
> > Also, in the DDL, SXes still have an sx_id, though as QOF entities they
> > are GUID-posessing objects, and thus should follow the pattern of those.
> 
> How closely should the DB Schema map to the QOF object model?

I think pretty closely ... if only to make the Object/Relational mapping
problem as simple as possible.  I've worked in situations where the
models have a high impedence mismatch, and it's just not fun. :(

In particular, here: I don't see a reason to have a seperate "sx_splits"
table with string credit/debit values when the QOF model is "real splits
with the credit/debit formulas in a kvp frame".

-- 
...jsled
http://asynchronous.org/ - `a=jsled; b=asynchronous.org; echo ${a}@${b}`
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20061027/48f69d76/attachment.bin