Database design issue

Thread view:

shaji.cc@gmail.com - 24 Aug 2005 14:40 GMT

Hi:

I have a database design issue. Let me first state the problem.

A user stores his transaction details like

order id, user id, order desc, amount spend, transaction currency

into a table. Each user has an own-currency associated with him.
But he is free to do transactions in any currency.

A report has to be generated for this table. Catch is that, who ever
is generating the report wants to see the amount in his own currency
(otherwise he cant total the amount).
So at the time of report generation, for each row, it has to calculate
the FX rates for that amount. This creates huge performance problem.

Introducing 2 or 3 columns more and storing amounts in diffrent
currencies was the first idea came to my mind. But this doesnt scale
well.

Can anyone suggest an idea to overcome this.

Thanks in advance.

Reply to this Message

Babu Kalakrishnan - 24 Aug 2005 20:08 GMT

> A report has to be generated for this table. Catch is that, who ever
> is generating the report wants to see the amount in his own currency
[quoted text clipped - 5 lines]
> currencies was the first idea came to my mind. But this doesnt scale
> well.

That's obviously a baaaaad idea.

I don't see why having to do the conversion on the fly is such a big
performance issue. I wouldn't expect more than say a couple of hundred
entries in the Currency conversion table which you could read into a
memory cache at the start of your report - unlikely to take you more
than say 10 to 20 msecs with any decent database - and performing the
conversion using an in-memory table will probably cost you very very
little in terms of time.

On the other hand, if you're trying to read the conversion table from
the database for every user record, THAT may be an issue. Each database
access costs time - so you'd certainly want to avoid that if your number
of user records is large.

BK

Reply to this Message

shaji - 25 Aug 2005 07:02 GMT

Thanks for your reply.
Yes, It is the second case you said. For every record you have to do a
database access (be cause, your forex rates are stored in that database
in a datewise manner)

Can you throw some inputs in this regard.

Reply to this Message

Luke Webber - 25 Aug 2005 08:52 GMT

> Thanks for your reply.
> Yes, It is the second case you said. For every record you have to do a
> database access (be cause, your forex rates are stored in that database
> in a datewise manner)
>
> Can you throw some inputs in this regard.

Why not use a join on the rates table and calculate the correct value by
multiplying the two columns together?

Luke

Reply to this Message

shaji - 26 Aug 2005 04:57 GMT

Thank you all for your inputs.

Reply to this Message

mike@sherrillshelton.net - 24 Aug 2005 21:53 GMT

>I have a database design issue. Let me first state the problem.
>
>A user stores his transaction details like
>
>order id, user id, order desc, amount spend, transaction currency

User ID might be a bad idea.

>into a table. Each user has an own-currency associated with him.
>But he is free to do transactions in any currency.

Ok.

>A report has to be generated for this table. Catch is that, who ever
>is generating the report wants to see the amount in his own currency
>(otherwise he cant total the amount).
>So at the time of report generation, for each row, it has to calculate
>the FX rates for that amount. This creates huge performance problem.

How are you doing the calculation?

Let's think about this.

"Mike" is a customer.
Mike's preferred currency as of today is US dollars.
Mike might move to Canada next year.
(That is, Mike's preferred currency might change to Canadian
dollars someday, but it might not, even if he moves to Canada.)

On 2005-08-01,
Mike buys a widget from "Foo, Inc." for 1243 Wibble-lire.
On 2005-08-02,
Mike buys two widgets from "Foo, Inc." for 2489 Wibble-lire.
On 2005-08-03,
Mike buys a widget from "Bar, Inc." for 12 pounds bentling.

Now, let's say that today, 2005-08-24, you want to print that report
you were talking about. Let's say that you want the "total amount"
Mike spent on widgets. And let's say that *your* preferred currency is
rubles. The "total amount" Mike spent is actually

3732 Wibble-lire
and 12 pounds bentling

But what do you expect on your report? Here are some (very
abbreviated) possibilities. There are more.

1243 Wibble-lire
* forex rate (Wibble-lire to rubles) on 2005-08-01.
...

1243 Wibble-lire
* forex rate (Wibble-lire to rubles) on 2005-08-24.
...

(1243 Wibble-lire
* forex rate (Wibble-lire to dollars) on 2005-08-01)
* forex rate (dollars to rubles) on 2005-08-01.
...

(1243 Wibble-lire
* forex rate (Wibble-lire to dollars) on 2005-08-01)
* forex rate (dollars to rubles) on 2005-08-24.
...

The fundamental design issue is that you have to know "the forex rate"
(which is a fuzzy term) at the "time" (another fuzzy) of the order. How
you calculate the "total amount" doesn't really matter.

But, if you're using a SQL database, a carefully designed and indexed
table of forex rates would probably give you good performance. It's
just an inner join on two columns, plus some simple arithmetic.

Signature

Mike Sherrill

Reply to this Message

Database design issue

Free Magazines