SX model changes [WAS: Updated DDL for SQL backend]

[Chris Shoemaker]

On Fri, Oct 27, 2006 at 12:31:40PM -0400, Josh Sled wrote:
> 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'd actually recommend going the other direction.

  1) eliminate all SX "virtual" accounts.
  2) Introduce a flag to Transaction: "is_template"?
  3) Make SX transactions out of real Transactions (with
is_template=true), with real zero-valued Splits, belonging to the real
accounts, with "scheduled" values as strings in the Split's KVP.
  4) Change xaccQueryGetTransactions() and
xaccSplitListGetUniqueTransactions() to ignore template transactions.

What do you think?

-chris

> > > 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}`



> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel