SQL backend for GnuCash 2

[Daniel Espinosa]

I'll response message by message, then this is for this:

2006/10/23, Phil Longstaff <plongstaff at rogers.com>:
> On Mon, 2006-23-10 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 added a Inventory Table (or Class) in wich you can have the
> > merchandise in a small business like a small store.
> >
>
> What version of Dia do you have?  I have 0.94, and when I load it, I get
> a number of messages about connection points which do not exist.
>

I have 0.95 on ubuntu dapper.

> It's a good start, but I have some issues with the schema:
>
> 1) Accounts: parent is not null.  However, the top level accounts
> (Assets, Liabilities, ...) don't have a parent.  In addition, the parent
> should be a foreign key reference back to the Accounts table.
>

parent could be set to NULL if the acount is a 'top level'.


> 2) You have a Currencys (should be Currencies) table.  However, tables
> which should have a foreign key reference to it just have type
> "integer".

Table is named 'currencies' now. The reference, if I understand you,
is by the ID in the currencies table, and it is a integer.

>  In addition, this should probably be a Commodities table to
> include stocks and mutual funds.
>

I originaly included the StockTransactions, as a table derived from
Transactions, now I renamed both to: 'commodities' (StockTransactions)
and 'trasactions' (Transactions). Remember that in the 'commodities'
table will have the same columns that 'transactions'.


> 3) An account can have a type which is a commodity (for stock or mutual
> fund accounts).

I have modified the types in the 'account_type' table, and now I have
substituted the 'stock' and 'mutual found' types by 'commodity' type

>
> 4) You need to split Transactions in Transactions and Splits.  A
> Transaction will include the date (currently, both entry date and
> posting date (the one you see)), description, commodity (currency?).  A

I have renamed the Transactions table to 'transactions' and added the
columns you refer, and the 'reconsilation_date' and
'reconsilation_state'

> Split will include the memo, reconciliation state and reconciliation
> date (end date of the statement), credit or debit amount (can be 1 field
> with +ve or -ve value), account, ...
>

I added the 'transaction_split' table where the you'll store the
information you refer before (see the table definition about)

> 5) The current XML schema stores other info with each Account.  The info
> seems to be related to recently or commonly used transactions.
>

This could be by filling the autocompleting future using a "SELECT
DISTINCT description FROM transactions", and the same when you fill
the splits.

> 6) You need to add a Price table for commodity prices and currency
> exchange rates.
>

I added the 'commodity_type' referer by 'commodities' and by the new
'prices' tables.

> I don't use the GnuCash business features, so I won't comment on those
> tables.
>

I'm planning to use them extensible and improve the business futures
in GnuCash, if I can of course :-)


-- 
Trabajar, la mejor arma para tu superación
"de grano en grano, se hace la arena" (R) (entrámite, pero para los
cuates: LIBRE)