DB design document

[David Merrill]

On Thu, Dec 14, 2000 at 09:57:38AM -0600, Bill Gribble wrote:
> 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. 

I don't even *see* a currency datatype.

So there is an account 'currency' AND an account 'security'? Why are
there two? How can an account have both a currency and a security,
when everything is just a commodity?

NEVER MIND. You answered this later on.

The security represents the commodity which the account is actually
measuring, and the currency represents the cash value, IF the security
is non-cash. It is used to verify we have a balanced transaction.

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

Can the user and/or admin add to the list of namespaces as well as
commodities? Could they add a namespace for internal commodities, for
example a namespace that means "corporate assets", under which they
have accounts for office furnishings and inventory?

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

So they are not actually defined in the commodities 'database'? There
are commodities in a commodities 'database', and those are combined
with the currencies at program load?

iow there are two separate data stores, one for currencies and another
for all other commodities, but when the program loads they are merged
into a single data structure?

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

Well this is a good time. As soon as I understand how they work
together I'll see how it might be achieved in the db.

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

Okay.

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

That's what I meant. I'm using the term rational numbers to indicate the
value/damount structure (gnc_numeric data type).

Okay. The _scu values are then the default values for damount for that
account. A split recorded against the account would have damount set
to the _scu value.

Although default, it could be modified later. Does it originally come from the
commodity's definition, because each commodity has some default value
associated with it? Certainly for currency commodities this would be
the case.

A related issue... Suppose you have an account with a _scu of 100, and
the user enters a transaction with an amount that requires it to be
bumped up to 1000. (They enter 1.001). We would then bump the damount
to 1000 and record the value as 1001? Other records would be
unaffected?

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

It gives me more of an understanding of the problem. I'll have to give
this one some thought. There seem to be many implications and
potential pitfalls.

-- 
Dr. David C. Merrill                     http://www.lupercalia.net
Linux Documentation Project                dmerrill@lupercalia.net
Collection Editor & Coordinator            http://www.linuxdoc.org
                                       Finger me for my public key

The system will be down for 10 days for preventive maintenance.