DB design document

Bill Gribble grib@gnumatic.com
Thu, 14 Dec 2000 09:57:38 -0600


On Thu, Dec 14, 2000 at 09:27:07AM -0500, David Merrill wrote:
> So currency means the unit of measure, e.g., 'USD'? But only world
> currencies, not anything else (bonds, whatever)? Because they always
> go in as securities.

We have abstracted the notion of currencies, stocks, widgets, and 
any thing else you can own a countable amount of to 'commodity'. 
The data type is gnc_commodity.  Both the 'currency' and the 'security'
of an account are pointers to structures of type gnc_commodity. 

A gnc_commodity has several fields, including an abbreviation
(mnemonic), full name, information about the subdivision of its units,
and so on. Information about gnc_commodities is a separate database
from your financial data, and is saved and restored with your
accounts.  If you wish to keep track of bricks, bottles of aspirin,
stock shares, pesos or anything else you need to make an entry in the
gnc_commodity database for it, and that gets saved with your data.

gnc_commodities are keyed in the database by the pair of 'namespace'
and 'mnemonic'.  the 'namespace' describes the type of the commodity;
it is "ISO-4217" for national currencies, "NASDAQ" for Nasdaq stocks,
"NYSE", "AMEX", etc.  'mnemonic' is an arbitrary string, but for
national currencies it is the 3-letter iso code (USD, etc) and for
stocks it is the ticker symbol.  There are cases where ticker symbols
are not unique between exchanges, and cases where ISO currencies are
also sticker symbols, so the Namespace is an essential distinction.

BTW, national currencies are treated specially; they are loaded from a
static database at startup time and aren't saved with your data.

Don't get tied up in the distinction between the account's "currency"
and "security".  Those terms are deceptive, because in many kinds of
transactions the roles they play are reversable.  In fact, as Dave
mentioned, we are trying to figure out a way to remove one of them
altogether.

> Why isn't three characters sufficient for currency codes? It looks
> like the existing code is using three characters; that's why I am.

The 3-letter code is just for printing.  Internally, the account
stores a gnc_commodity, and so should any backend.

> > security_scu & currency_scu: 'scu' is Smallest Convertable (I think)
> >   Unit, the denominator used for amounts in security/currency.
> >   Commodities have default scu's, but accounts can override them.
> >   For example, you might have stock in two different brokerages.
> >   One may track your shares to 4 decimal places, and another to
> >   5 decimal places, even if they're the same shares. This is not
> >   an academic example.
> 
> This is closely related to the whole rational number mechanism, right?
> Is this the number that will appear in damount when a split is
> recorded against this account?

Nope, it's the denominator of gnc_numerics put into the value/damount.
For example, if you use Fidelity and they report your holdings of a
mutual fund to 5 decimal places, the account's security_SCU is 100000.
(you hold a counted number of 1/100000 shares).  The currency_scu is
likely to be 100 if you are using USD.


> > Note: we've considered moving currency to transactions. This may
> >       still happen in the future.
> 
> Do you mean I should be prepared to add the currency_code field to the
> transaction table? Shouldn't it be the split table?
> 
> Why would you do this? So you can have transactions in more than one
> currency in a single account? Why would you need that capability?

The issues are sort of tricky and our solution is not completely
worked out, but there is a real problem with the current system which
I'll try to explain.

Remember that 'transactions' are NOT part of an account.  They are
collections of splits, each one of which is part of an account.
Transactions almost always involve more than one account, and they
don't belong to any one account more than any other.  This makes
sense; if you move money from one bank account to another, does that
'transaction' belong more to one bank account than the other?  No.

So, about commodities, security, currency: if you think about it, each
account is really an inventory of a particular kind of commodity.
USD-denominated bank accounts are inventories of dollars, for example,
and stock accounts are inventories of stock shares.  So it makes sense
that we have a gnc_commodity associated with each account; in Gnucash
right now, the 'thing that is being counted' is the account's
Security.  This is a little confusing, because many accounts don't
even have a Security, but in fact under the covers if there's no
Security it's assumed that the Security is the same as the Currency.

The reason we currently have two commodities associated with an
account is pragmatic rather than theoretical.  In order to verify that
'credits equal debits' in a transaction (the 'double entry' criterion
which measures if the transaction is balanced) we have to be able to
add all the splits up in a common currency so that we can compare them.
In any kind of commodity-exchange transaction, such as buying USD 
with Euros or buying IBM shares with USD, you have a problem; a
transaction might look like this:

Account		   Debit                  Credit     
Bank account		                  USD 10000
IBM holdings       IBM 100

So how can you tell if that transaction is 'double-entry safe'?  You
need a common valuation currency for all the splits: 

Account		   Debit	          Credit     
Bank account			          USD 10000 (USD 10000)
IBM holdings       IBM 100 (USD 10000)

Now it's clear that 100 IBM shares are worth USD 10000 in this
transaction, so it's balanced with USD being the common currency.

In Gnucash currently, each split is valued in two commodities, the
account's "security" and "currency".  When checking the balanced-ness
of a transaction, we look at all the splits to see if they are valued
in a common commodity and if not we blow chunks.  For the most part,
people operate in one "national currency" which is the same for all
their accounts so it's not a problem.

However, the current system falls down completely when you want to do
something as simple as move money from an account denominated in one
currency to an account denominated in another (say if you have a EUR
Cash account for your trip to Europe, which you fill buy buying EUR
with USD).  It requires an intermediate 'currency' account which I
won't go into the details of but let's just say it's an abomination.

If we get rid of the currency in an account and move it into the
transaction, we can much more directly say what we want about
transactions involving multiple currencies by just requiring that we
have a value in a common currency for every split.  I know that's not
really all that detailed but my fingers are tired and I haven't had
any coffee yet.

Bill Gribble