SQL backend for GnuCash 2

[Derek Atkins]

"Daniel Espinosa" <esodan at gmail.com> writes:

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

Not in your design; you have it declared "NOT NULL".

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

References should be by GUID.

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

You need three tables:

  Transactions
  Splits
  Commodities

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

You don't need this table, and indeed including it could cause
confusion.  This table is already defined in src/engine/Account.h,
making sure they keep in sync would be a major nightmare.

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

No, you don't understand..  You really need two tables.  And the
reconciliation are on the Split, not the Tranasction.

Thanks!

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available