SQL backend for GnuCash 2

[Josh Sled]

On Mon, 2006-10-23 at 17:14 -0500, Daniel Espinosa wrote:
> Attached you'll find a DataBase Schema for GnuCash.
> 
> This is in order to bigin the development to support DB backend (and
> replase the actual file one).

I agree with Phil's comments.  Here's some more:

Simple conventions on things like this are important; here's the ones
I've come to like:
  - tables are in lowercase, plural word form.
  - '_'-separated words
  - lowercase unless there's a really good reason.
  - there are no 'id' columns, only 'thing_id'.
  - foreign id columns have '_id' at the end (e.g, 'parent_account_id')
  - 4 space indents
  - SQL statements in upper-case


It'll probably be the case that the SQL DDL statements will be easier to
work with than these pictures.  Just plain text like...

    CREATE TABLE accounts
    (
      id int NOT NULL,
      name String,
      parent int NOT NULL, -- fk(Account.id)
      [...]
    )

...is better.



I'm a big fan of surrogate/artifical keys.  It helps -- even informally
-- to have them indicated as "foreign key"-like things, which you've
generally done here.  Though in combination with convention that there
are only 'related_thing_id' columns, it's usually clear from the column
name what the relational constraint should be.



You should see how gnucash represents the following items:
  - numeric values (gnc-numeric)
  - commodities (both currencies and stock)
    - the PriceDB
  - Transactions
  - Splits
  - KVP frames

 You can do this from the code (in src/engine/) or from opening your
datafile and seeing how it's represented in the XML.  In particular,
there is interaction between Splits, GncNumeric and the Commodity that
makes a seperate "StockTransaction" table unnecessary.



Here's a sketch of a schema for scheduled tranactions:

CREATE TABLE scheduled_transactions
(
    sx_id int identity not null,
    name string not null,
    auto_create boolean not null,
    auto_create_notify boolean not null,
    create_days int null,
    remind_days int null,
    start_date datetime not null,
    template_account_id int null, -- fk(Accounts.id)
    -- @fixme: FreqSpec stuff: I'm not going to detail this here,
    -- since this might change dramatically in the near future.
    -- We can talk about it later...
    last_instance_count int not null,
    last_occur_date datetime not null
)

CREATE TABLE scheduled_transaction_saved_instances
(
    postponed_instance_id int identity not null,
    sx_id int not null, -- fk(scheduled_transactions.id)
    instance_date datetime not null,
    instance_count int not null
)

-- 
...jsled
http://asynchronous.org/ - `a=jsled; b=asynchronous.org; echo ${a}@${b}`
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20061023/c330b039/attachment.bin