 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / June 2005Handling Money Via UDT / UDF (i.e. homegrown "extender")
Folks, I've been exercising my mind recently about the complexities of implementing a "currency" data type within DB2 to cope with multiple currencies. A monetary value is often simply represented as a DECIMAL column : for example many times I've seen DECIMAL(12,2) used. The issue with this is that there is nothing to interpret what currency this relates to (US dollars, Canadian dollars, Euros, British pounds or whatever), and a mixture of currencies and the SUM() function could be lethal. So I thought that it would be a good idea to build an "extender" to handle the situation (by "extender" I mean a set of complex user defined types and related user defined functions to go with them). I'm wondering if anyone else had done such a thing, and knows of any code which is freely available ? My initial thoughts were that the "currency" type consists of at least the following elements - MONETARY_VALUE DECIMAL(12,2) CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe We'd need homegrown versions of the SUM() and other similar functions. We'd also need functions to do conversions from one currency to another (and potentially some tables to store conversion rates : perhaps we need a date in the "type" as any conversion will be at a specific exchange rate for that date ? All thoughts welcome. Sorry if this is a bit unusual topic, but it interests me ... sad I know. Phil > Folks, > [quoted text clipped - 26 lines] > in the "type" as any conversion will be at a specific exchange rate for > that date ? This is a good thought and I think a feasible implementation. -- Matt Emmerton > Folks, > [quoted text clipped - 32 lines] > > Phil I would NOT use this approach for a few reasons: 1. Performance will suck 2. There are limits on LOAD, EXPORT, IMPORT of structured datatypes 3. Did I mention that performance will suck? With performance I don't mean raw performnec of structured types. No problem there, but what happens when you try to compare two values? You need to compare through some funny functions (normalize to a predefine(yet floating) currency)? Selfdescribing datatypes spell death for a system geared towards optimizing based on static information.... The standard solution is to define one distinct type (UDT) per currency. I.e. you can have a EURO based on DEC(12, 2) and a USD on DEC(12, 2). Then when you compare EUROs to EURO's you can do native compares, indexing, hash-joins, .. all the good stuff. You can also add some comparison functions between the currencies, perhaps using a webservice to look up the latest exchange rates. Cheers Serge  SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab Serge, "The standard solution is to define one distinct type (UDT) per currency." that leads to tons of tables like PAYMENT_USD, PAYMENT_CAD, PAYMENT_EURO, whatever, and all these tables having almost identical structure / RI / Check constraints etc. Looks like an anomaly to me - storing data (currency code) in metadata. Maybe it would be simpler to: 1. have one single table PAYMENT ... MONETARY_VALUE DECIMAL(12,2) CURRENCY_TYPE CHAR(3) ... 2. create a set of UDDTs for all the currecncies you need 3. Create views WITH CHECK OPTION on top of single table PAYMENT and expose your data via those view only. Have these views convert MONETARY_VALUE to the UDDTs Does it make sense? > Serge, > [quoted text clipped - 25 lines] > > Does it make sense? Yes, if that is the need then I agree. But how many cases are there where arbitrary currency values are held in a table? At my bank I can have an account in CAND and USD, only... Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > But how many cases are there where arbitrary currency values are held in > a table? > At my bank I can have an account in CAND and USD, only... I have to think that a company such as IBM itself would deal in arbitrary currencies. I would imagine each national "branch" would deal in its own currency for most transactions (plus maybe USD), but then when producing the quarterly results, everything needs to be tied back to USD for SEC reporting. Banks, too, will deal with even more "currencies": most banks allow you to buy/sell other currencies at some given rate loosely based on the market. And then do other currencies count? Bonds, mutuals, stocks, handled by most trading companies - are these currencies? One could really abstract the heck out of this, I would think. Just because I can only get CDN at my local ATM, or I can only open CDN and USD accounts at my local branch, doesn't mean that's all the currencies a bank deals with ;-) >>But how many cases are there where arbitrary currency values are held in >>a table? [quoted text clipped - 5 lines] > then when producing the quarterly results, everything needs to be tied > back to USD for SEC reporting. Are you telling me you would track yoru revenue centrally, but not convert to your "master" currency directly. I think I would take serious offense with such a design... Also the question whether conversion rates are those of the booking date at the subsidiary or the report date plays in here. Im CS and not MBA.. so I shut up on that one.... > Banks, too, will deal with even more "currencies": most banks allow you > to buy/sell other currencies at some given rate loosely based on the > market. Yes, OK.. but these are point in time transactions. Your account will NOT show that you received 100 EUR. It shows you received a bucket of CAND with a nice annotation on that this was originally 100 EUR converted with a rate of 1/x bucket. > And then do other currencies count? Bonds, mutuals, stocks, handled by > most trading companies - are these currencies? One could really > abstract the heck out of this, I would think. And it's not a new problem, and it has been solved quite satisfactory long before structured types came around. Banking and OO.. never seen that combination. > Just because I can only get CDN at my local ATM, or I can only open CDN > and USD accounts at my local branch, doesn't mean that's all the > currencies a bank deals with ;-) That wasn't the question. The question is what do I need to store persistently and in general. No question there are cases where I want to store holdings with a qualifying currency columns, but that sure won't be the common case. Cheers Serge SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab Decimal(12,.2) may be adequate for a piggy bank, but is way too small for the currencies and sums dealt with in the real world. In fact, it's not even adequate to deal with simple things like the balance of the bank I work in (expressed in USD). Another problem you face is that there's no such thing as AN exchange rate, so that a function as simple as adding two values in different currencies has no single answer. Exchange rates are variables within a range, where the range of values tends towards one side or the other, depending on whether you're buying or selling. I fear that your view of the world is too simplistic. That's why there's no "free code" available. > Folks, > [quoted text clipped - 35 lines] > > Phil |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.