SQL backend for GnuCash 2

[Derek Atkins]

Hi,

A few more comments (my previous comments directly to Phil still
apply).   I just looked at the Dia drawings, and I've got a few
more comments.  Note that you didn't send out an updated doc, so
these comments are based on the original version:

1) We don't need an AccountType table.  AccountTypes are not data,
   they are encoded in the application.  There's no reason to add
   them to the database because they are constants.

2) You don't need a StockTransaction table.   You should model the
   Transaction and Split tables like the current PG backend.

3) You should model the Business features closer to the actual objects.
   This means you should have an Address table and then link to that
   to/from the Customer, Vendor, and Employee tables.

4) I wont comment on Inventory because gnucash has no inventory support,
   except to say that adding the table later when there's real support
   is easy.

Daniel, I think that right now Phil's schema is much closer to what
we want/need, but I think we're on the right path.  Phil, you might
want to take the time to explore src/business/business-core/file/*
to see the various business objects for mapping to SQL.

Thanks,

-derek

Quoting Daniel Espinosa <esodan at gmail.com>:

> 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)
>
> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>



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