Exchange rate table and conversion on the fly in query

database-designmoneytimestamptype conversion

I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items.

I'm working with Java and my first thought was that I would use some kind of API to pull the rates and post process (loop) after I get the the items from the DB. But I'd rather not have to unnecessarily loop if I can directly get the right price form the query.

So I think I'm going to store all the exchanges rate in the DB and have my query do the conversation job here is my first draft:

 CREATE TABLE currency (
      code VARCHAR(3) NOT NULL,
      symbol VARCHAR(3) NOT NULL,
      display_name VARCHAR(4) NOT NULL,
      PRIMARY KEY (code),
      UNIQUE KEY uq_ccode (code)
    );

CREATE TABLE exchange_rate (
  currency_code_from VARCHAR(3) NOT NULL,
  currency_code_to VARCHAR(3) NOT NULL,
  rate numeric(15,4) NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (currency_code_from, currency_code_to),
  FOREIGN KEY (currency_code_from) REFERENCES currency(code) ON DELETE CASCADE,
  FOREIGN KEY (currency_code_to) REFERENCES currency(code) ON DELETE CASCADE
);

I would then write a query like:

select price as originalPrice, (price * rate) as TargetPrice FROM .....
JOIN exchange_rate er ON er.currency_code_from=(the currency of the item) AND er.currency_code_to=(the user currency)......

Note that on the above tables I would have to have every single combination

GBPEUR
EURGBP
GBPGBP (well i guess i can probably avoid this one if a slighty amend the query to detect this scenario)

Plus.. I would have to have some kind of cron job to daily update this table.

Now I'm far to be a banker nor a SQL genius so I would like some input from people that have already written such tables/query and that have expertise in this kind of stuff

Am I fare off with my design? Would I be better off post processing the results and do the conversation in java with rates coming from an API?

Best Answer

How are your underlying item prices stored?

If, for example, your items are all priced/stored in EUR, then there is no need for an NxN (duplicative) currency matrix.

In this case, exchange_rate (against EUR or your base currency) can simply be a field in the currency table (vs an independent table).

Then all of your conversions for display (which are all then simply EUR->XXX) can be calculated with a single JOIN.

Related Solutions

SQL Server – Best Practices for Handling Language

If I understand your requirements correctly....

I would just use the natural key, LanguageCode-CultureCode ("en-US," for example). It's small enough. (I'm using the entire "en-US" as the primary key to differentiate it from "en-GB," for example.)

CREATE TABLE [dbo].[Language](
    [Language] [char](2) NOT NULL,
    [Culture] [char](2) NOT NULL,
    [LanguageCode]  AS (([Language]+'-')+[Culture]) PERSISTED NOT NULL,
 CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED 
(
    [Language] ASC,
    [Culture] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [LanguageCode] UNIQUE NONCLUSTERED 
(
    [LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Language_Text](
    [LanguageID] [varchar](5) NOT NULL,
    [LanguageCode] [varchar](5) NOT NULL,
    [LanguageName] [nvarchar](20) NULL,
 CONSTRAINT [PK_Language_Text] PRIMARY KEY CLUSTERED 
(
    [LanguageID] ASC,
    [LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Language_Text]  WITH CHECK ADD  CONSTRAINT [FK_Language_Text_Language] FOREIGN KEY([LanguageCode])
REFERENCES [dbo].[Language] ([LanguageCode])
GO

ALTER TABLE [dbo].[Language_Text] CHECK CONSTRAINT [FK_Language_Text_Language]
GO

This should allow you to get all articles in en or sv, and you can also query for en-US or sv-SE. Presumably, although it wasn't in your example, you could also query for en-CA, fr-CA, en, fr, or CA.

Edit--I'm sorry, you're right, no search by Culture in my old code. Revamped above, sorry. Here's a sample of content:

Language:

Language-Culture-LanguageCode
en  US  en-US
sv  SE  sv-SE

Language_Text:

LanguageID-LanguageCode-LanguageName
en-US   en-US   English
en-US   sv-SE   Engelska
sv-SE   en-US   Swedish
sv-SE   sv-SE   Svenska

Searching by Culture (Canada):

SELECT        test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM            test.dbo.Language_Text INNER JOIN
                         test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE        (test.dbo.Language.Culture = 'CA')

Searching by Language (French):

SELECT        test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM            test.dbo.Language_Text INNER JOIN
                         test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE        (test.dbo.Language.Language = 'fr')

Searching by LanguageCode (Swedish):

SELECT LanguageName
  FROM [test].[dbo].[Language_Text]
  where (LanguageID = 'sv-SE')

Why is add timestamp column with default not null so slow in Oracle 11g

This is actually improved in Oracle 12c.

From Oracle Magazine's Ask Tom column: http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html

In Oracle Database 11g you were able to perform a fast add of a column to a table if it had a default value and was defined as NOT NULL. (Arup Nanda has written about this at bit.ly/16tQNCh.) However, if you attempted to add a column with a default value and that column permitted null values, the ADD COLUMN operation could take a significant amount of time, generate a large amount of undo and redo, and lock the entire table for the duration of the operation. In Oracle Database 12c, that time, volume, and locking are no longer part of the process.

I know this doesn't help if you're stuck on 11gR2, since you probably want to get that column added before taking an outage to upgrade the DB.

As to why it takes so long only when the data type is TIMESTAMP, I'm at a loss. Maybe since the TIMESTAMP data type is a few more bytes to store than DATE, or than VARCHAR2 for empty strings, and those extra bytes are causing a cascade of row migrations. To test that theory, try adding a fixed length CHAR column (in a non-Prod environment): alter table my_table add my_char_col CHAR(200) default 'hello' not null; I expect this would not be instantaneous due to row migrations.

Your best bet on 11gR2 might be either to

Take an outage at an appropriate time that is ok with your stakeholders to add this column

Use DBMS_REDEFINITION to add the column as part of an online redefinition. This should be non-blocking, and even though it may take hours, the users will not even notice it happening (no locking). If you have a lot of indexes, this becomes a little more difficult. If you have foreign keys, this becomes even more difficult. If you have stored procs or views that reference this table, be sure to recompile them after you FINISH the redef. This will have a side-benefit that your table will be reorganized (i.e. defragmented).

Maybe add the column without the NOT NULL constraint. When that finishes, then alter the column to add the NOT NULL constraint. My intuition is that you'll still get locking somewhere in the process, so no promises. Maybe just a crazy idea.

If you want to try the Online Redefinition, then you might find these links helpful:

Best Answer

Related Solutions

SQL Server – Best Practices for Handling Language

Why is add timestamp column with default not null so slow in Oracle 11g

Related Question