Schema

David Merrill dmerrill@lupercalia.net
Tue, 12 Dec 2000 17:37:05 -0500


On Tue, Dec 12, 2000 at 02:09:17PM -0800, Dave Peticolas wrote:
> 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.

I'd recommend we use the standard GUID including MAC where it is
available. If there is no MAC, then we could fallback to some
arbitrary 48 bit pattern entered in the config table, or do without it
entirely. After all...

Q: How often would a database back-end NOT have a MAC address? 
A: Only when running a local-only application. In which case, you
don't NEED the MAC address to guarantee uniqueness, right?!?!?

> > > >
> > > > 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.

Added to my notes, thanks.

-- 
Dr. David C. Merrill                     http://www.lupercalia.net
Linux Documentation Project                dmerrill@lupercalia.net
Collection Editor & Coordinator            http://www.linuxdoc.org
                                       Finger me for my public key

Rev. Jim:	What does an amber light mean?                                 
Bobby:		Slow down.
Rev. Jim:	What...   does...  an...  amber...  light...  mean?
Bobby:		Slow down.
Rev. Jim:	What....     does....     an....     amber....     light....