Schema

[Dave Peticolas]

David Merrill writes:
> On Wed, Dec 13, 2000 at 06:42:04AM +1000, Phillip J Shelton wrote:
> > David Merrill wrote:
> > 
> > > I'm beginning to work on the database schema for an eventual SQL back
> > > end. I am keeping my notes in an ASCII file, which will always be
> > > available at http://www.lupercalia.net/schema.txt
> > 
> > some comments and suggestions.
> > 
> > > SPLIT
> > > -----
> > > account_guid                    char    32      ->account.account_guid
> > > transaction_guid                char    21
> > > ->transaction.transaction_guid
> > >                                         ^
> > >                                         Aren't GUIDs 32 bytes?
> > 
> > Um, could you explain GUIDs? I am lost.
> 
> Globally Unique ID, also known as UUID or Universal Unique ID. It is a
> 32 byte value usually calculated using the machine's MAC address and
> the time, plus some random stuff, plus more stuff I don't understand.
> It is guaranteed to be unique.
> 
> The specification on UUIDs is available from The Open Group at
> http://www.opennc.org/onlinepubs/9629399/apdxa.htm

Our GUIDs are generated by md5ing a larger set of random stuff.
The Open Group specification requires a MAC address, which not
everyone has.

Within a particular set of accounts, our GUIDs are guaranteed
unique. Between sets of accounts, there is a possibility (though
extremely small) that there are duplicates.

I noticed in your webpage you bring up the issue of using GUIDs
vs serial numbering. I also favor GUIDs. Furthermore, the OFX
standard uses both server- and client- generated GUIDs, so we
can use them for that purpose as well.


> > >
> > > TRANSACTION
> > > -----------
> > > transaction_guid                char    32
> > > transaction_date                date            date of event
> > 
> > These need a note field as well.
> > 
> > A transaction is the unit that is entered in the ledger, and the splits
> > are for which accounts are affected by the transaction.  Others will
> > have a better explanation I am sure.
> 
> So the note field is equivalent to "memo" in your register? There is
> already a memo field in the SPLIT table. Isn't it redundant to have it
> here?

We recently added a 'notes' field that is specific to transctions.
The 'memo' field in splits is the memo field in the register. Having
it displayed both with the transaction line & the split line was
causing confusion & gui inconsistency, so we added a notes field
to transactions that will go where the 'memo' is in double-line modes.

dave