Splitting the slots table

[John Ralls]

On Jan 2, 2011, at 2:34 PM, Phil Longstaff wrote:

> Just thinking ahead to conversion of the sql database to a true
> database, not just storage which is what it currently is.
> 
> For most tables, we could add FOREIGN KEY constraints so that in the
> splits table, for example, the tx_guid which specifies the transaction
> to which the split belongs must be a valid guid key in the transactions
> table, and the same for the account_guid and the accounts table.
> 
> I do happen to know there at one point when a lot is being created, it
> can be saved with account=NULL, so that the NOT NULL constraint was
> removed from the lots table account_guid at one point.  However, we
> should be able to modify the code so that a lot is never saved with
> account=NULL.
> 
> Back to the slots table.  The obj_guid field which indicates which
> object this slot belongs to can refer to a guid in any other table, so
> we can't have a meaningful FOREIGN KEY constraint.  Should we split the
> table so that we have account_slots, tx_slots, split_slots, etc. tables,
> one for each object type?  Each xxx_slots table would have an obj_guid
> field which would have a FOREIGN KEY constraint referring to the xxx
> table.
> 
> The slots table can hold slots of type "GUID" which contain a reference
> to another object.  Unfortunately, there's no FOREIGN key constraint we
> could use unless we split that field to have an account_guid, a tx_guid,
> a split_guid, etc.

We need to re-think KVP entirely: It doesn't match up very well with the relational model. 

A couple of examples:

Splits use KVP to store memos. Good, because not everyone uses them on every split, and there's no point wasting the space. But we can provide a split-memo table with a foreign key into the splits table (or vice-versa). That will be much faster to query (no WHERE name= clause in the join) and the data design will be clearer.

The HBCI (online banking) setup, on the other hand, is contained entirely in a hierarchy of KVPs. This makes some amount of sense in XML, but it's insane in an RDB. RDBs don't like recursion, and there's no way to do arbitrary hierarchies without recursion. HBCI needs its own tables.

I think that the first step is to work through all of the code and make an ERD for the existing model, documenting the use and structure of KVP. (Pretend for the purpose of this exercise that every use of KVP is a separate entity). Then we can normalize it into a good relational model and work out a transition path.

I have some more Gtk stuff to do over the next couple of weeks, but I'll start on the ERD after that.

Regards,
John Ralls