SQL backend for GnuCash 2

[Daniel Espinosa]

2006/10/23, Josh Sled <jsled at asynchronous.org>:
> 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
>

This is done the attached schema.

>
> 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 agree that the final work around the schema will be that
definitions, but consider that most of them could be diferent from
database provider to database provider, then this could be done when
you implement the final schema.

Think that the relations between tables is easier if you see them in diagrams.


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

The name of the column is renamed to referer to the column id the foreing key.


>
> 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}`
>
>

Let me study this items, but at first, I could say that the way
GnuCash handles the numeric values and representation, could be out
the scope of the schema becouse it will show how the values are stored
in the database and how they are related with others.

-- 
Trabajar, la mejor arma para tu superación
"de grano en grano, se hace la arena" (R) (entrámite, pero para los
cuates: LIBRE)
-------------- next part --------------
A non-text attachment was scrubbed...
Name: GnuCashDBSchema-0.1.dia
Type: application/x-dia-diagram
Size: 7085 bytes
Desc: not available
Url : http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20061025/8890d86f/attachment.bin