Splitting the slots table

[John Ralls]

On Jan 3, 2011, at 10:24 AM, Phil Longstaff wrote:

> See below prefixed by [Phil].  I've also prefixed Derek's comments with [Derek] 
> where there might be confusion.
> 
> Phil
> ---------
> I used to be a hypochondriac AND a kleptomaniac. So I took something for it.
> 
> 
> 
> 
> ________________________________
> From: Derek Atkins <warlord at MIT.EDU>
> To: John Ralls <jralls at ceridwen.us>
> Cc: Phil Longstaff <plongstaff at rogers.com>; Gnucash Devel Mailing List 
> <gnucash-devel at gnucash.org>
> Sent: Mon, January 3, 2011 12:00:14 PM
> Subject: Re: Splitting the slots table
> 
> John Ralls <jralls at ceridwen.us> writes:
> 
>> We need to re-think KVP entirely: It doesn't match up very well with
>> the relational model.
> 
> Yeah.  The KVP model works great for XML extensibility.  It does suck
> for SQL extensibility.
> 
> [Derek] As we move forward I think we need to think about both methods.  We have
> some requirements in that object A needs to somehow mark/tag object B
> but Object B doesn't know about said tagging.  I'm not sure how you go
> about doing that in a full relational database.  I suppose you could
> have a "object B tag from object A" table using a foreign key, but then
> you have to do a table join to get the data out,
> 
> [Phil] Which object would do the join?  For object B to load the info, it needs 
> to know about object A's table.  I suppose object A could load a sub-object and 
> tack it onto object B.  That might, in fact, be the best option.  Object B loads 
> its stuff as normal.  Object A then loads its stuff into a struct and registers 
> that struct with object B using the name "Object A's additions".  It could then 
> get its stuff back, and when registering, there could be added callbacks to be 
> used when object B is deleted or saved.
> 
> [Derek] and you need to make
> sure that you remove the data from this new table if the object is
> removed from the main table.....
> 
> [Phil] Can this not be done with the foreign key constraint using ON DELETE 
> CASCADE (I think that's it)?
> 
> It all gets very complicated very quickly.

So what's wrong with joins? They're what give RDBs their power, and good RDBMSes optimize them heavily. Commonly used joins can be precalculated with views.

Object A will carry a reference to Object B and whatever additional information is needed by Object A. If there's a one-A-to-many-Bs, then that gets put in a separate table with A's id as primary and foreign and B's as just foreign. On-delete and on-update constraints can be used to cascade deletes where appropriate, and triggers can be used to handle more complicated situations. For example, if a field in A's B-tag table is calculated from a field in B, then a trigger can cause that B-tag to be recalculated when B is updated.

Regards,
John Ralls