Re: Database theory and money

From: The Big Cheese <gouda_at_mybigcheese.com>
Date: Tue, 12 Sep 2000 14:34:04 -0400
Message-ID: <39be72be$1_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. 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?

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.

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 ... and another problem is that it makes writing such a statement, even for the case where there are no new currencies, very difficult since you can't iterate over a range of attributes in SQL.

The remaining problem is what to do with non-decimal currencies ... if there are any left. The old British system needed it's own domain! (better handled by storing things as decimal and converting on the way in and out).

"Jan Hidders" <hidders_at_REMOVE.THIS.win.tue.nl> wrote in message news:8pibtr$qf4$1_at_news.tue.nl...
> Chad wrote:
> > I just want some feedback on storing monetary values of different
> > currencies in the same column in a table. I think it violates
 relational
> > principles.
> >
> > Does anyone have a good source on this?
>
> Look at "An Introduction to Database Systems" by C.J. Date. One principle
 is
> that every column contains only values from a certain domain. Such a
 domain
> is a set of (atomic) values with a specific meaning. So the domains
 'weight'
> and 'height' may contain the same values but still are different domains.
 An
> important property of values within a domain is that it must make sense to
> ask if they are equal or not. For example, it usually does not make sense
> to ask if weight X is equal to height Y, so these should be different
> domains and therefore never in the same column.
>
> Whether this is the case in your table depends on what you exactly store
 in
> the column. If it is just a number and the monetary unit is not indicated
> anywhere then you probably have a problem anyway. If the monetary unit is
> indicated somehow in another column then this may or may not violate the
> principle mentioned depending upon whether you think it makes sense to
> compare numbers of liras to numbers of dollars et cetera. (I think it
 does,
> so as far as I am concerned you are not violating the principle.) If the
> monetary unit is indicated in the same column then you are violating the
> principle that values in columns should be atomic, i.e., cannot be split
> into meaningful parts. But this latter principle is usually not considered
> very important anyway (ususally a date is represented in one column
 although
> by this principle it should be split).
>
> --
> Kind regards
>
> Jan Hidders

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

  • Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Received on Tue Sep 12 2000 - 20:34:04 CEST

Original text of this message