Re: Database theory and money

In article <39be72be$1_at_post.usenet.com>, The Big Cheese <gouda_at_mybigcheese.com> writes
>**** Post for FREE via your newsreader at post.usenet.com ****
>
>Well the concept of domain is pretty flexible ... if you say I need
>different domains for each currency, then the database needs redesign when a
>new currency is introduced which is pretty bad, even in theory.

If you don't have a method for converting numbers in different currencies then they are different domains. If you add the conversion rates then the data can be converted into values in a single domain. But you do have to do that conversion somehow.

> What about
>the attribute "units" in standard order processing system? Should I have a
>different attribute for each unit (each, dozen, pair, gallon, liter, ounce,
>yard, meter, etc.) that I use for measuring items for sale? Then when a
>vendor packs things 9 to a box instead of eight, I need a database redesign?

That depends. If you know that a dozen is a synonym for the number 12 then you know the conversion rate. Someone who doesn't speak any English might not know that, and so for them "dozens" and "pairs" can't usefully be considered to be in the same domain.

>
>The most common way to handle it is to have an attribute for number of units
>and an attribute for currency id which is a foreign key into the currency
>table.

That's useful for converting values but not sufficient unless you add some more information, such as a time and date of the transaction. (I assume that the problem really is storing values and not the quantity of units of currency, such as banknotes.) To normalise foreign currency values to a single domain you need to know which currency and the time, because the conversion rate is time-dependent. So the currency identifier and the time are a compound key into the exchange-rate table.

Implementing a table like that is non-trivial, particularly when there might be a dozen or more different exchange-rates between the same currencies, depending on exactly when and why you need to compare them.

Many years ago I worked in a company that reported in dollars, bought in dollars and gulden but sold in sterling. We had a computer whose entire job was to track exchange-rates between USD, HFL and GBP. We replaced him with a spreadsheet. When anyone asked how much profit we made he had to ask which currency they wanted the figures in and what they wanted them for.

>
>One reason having a different attribute (column) for each currency is that
>introduction of a new currency invalidates any existing sql statement that
>attempts to find, for example, all orders over $10,000 US equivalent

Only if you try to simplify the table and include all of the values in one table. It may be necessary to use a join between the value table and the exchange-rate table, using the currency ID and a time. The time might be the time of the original transaction or it might be the time at which the currency is actually converted, or it might be the time that the selection is executed, or something else. It all depends on precisely what question you are trying to answer.

What you can do is to use a nominal exchange-rate at the time the transaction takes place, and record that in the value table. That does make single-table selections possible, but at a price. Firstly the data is denormalised, as the converted value is dependent on the transaction ID, the currency and the time. Secondly it's confusing because the converted value is only a snapshot, and will be invalidated by exchangerate  variation.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk