Database Schema

Question

Database Schema

kiran 4243

Mr or Mrs. 500

Points: 553
More actions
January 27, 2015 at 12:46 am

#294743

Hi,
I need to create a Forex table to maintain monthly currency exchange rate for each year. I can visualize 2 schema's but i am unable to decide the best one.
please advise.
Schema 1:
CREATE TABLE [dbo].[FX](
[Uniid] [int] IDENTITY(1,1) NOT NULL,
[Fx_Id] [nchar](10) NULL,
[Fx_Year] [smallint] NULL,
[Fx_1] [smallmoney] NULL, -> Jan Rate
[Fx_2] [smallmoney] NULL, -> Feb Rate
[Fx_3] [smallmoney] NULL,
[Fx_4] [smallmoney] NULL,
[Fx_5] [smallmoney] NULL,
[Fx_6] [smallmoney] NULL,
[Fx_7] [smallmoney] NULL,
[Fx_8] [smallmoney] NULL,
[Fx_9] [smallmoney] NULL,
[Fx_10] [smallmoney] NULL,
[Fx_11] [smallmoney] NULL,
[Fx_12] [smallmoney] NULL, -> Dec Rate
Schema 2:
CREATE TABLE [dbo].[FX](
[Uniid] [int] NOT NULL,
[Fx_Id] [nchar](10) NULL,
[Fx_Year] [smallint] NULL,
[Fx_Month] [tinyint] NULL,
[Fx_Rate] [smallmoney] NULL,
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 1

I would pick the smallest definition that fits your requirements, but stick with Dr Codd's rules[/url] as tight as possible !

CREATE TABLE [MyFinancialSchema].[FX](

[Uniid] [int] NOT NULL primary key,

[Fx_Id] [int] NOT NULL, /* declare the foreign key relationship ! and provide an index for it */

[Fx_Month] [date] NOT NULL, /* <- check this is a date which points to day 1 of the month yyyy-MM-01*/

[Fx_Rate] [smallmoney] NULL )

Questions remain:

Does smallmoney cover your rate needs ?

Does fx_rate need to be nullable ???

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

kiran 4243 Mr or Mrs. 500 Points: 553 More actions · Answer 2

Hi,

thank you very much for your valuable support and suggestions.

small money data type will cover my requirement.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

The thing that makes me thingk neither schema will work is that forex rates dont change nicely at month boundaries. Any organisation that pretends they do will either run foul of tax authorities or lose customers very rapidly, or maybe both plus go bust quickly, depending on who loses money from the stupidity of pretending the changes only happen once a month. Som neither schema is useful for anything real.

Tom

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

I have to agree with Tom, exchange rates are slippery devils and although it may seem like you want to have a monthly rate now, as soon as someone starts thinking about it, you will probably want a spot rate - in other words, a date/rate combination rather than a month/rate combination.

You can have rate changes in the middle of a month, which neither of your designs can handle.

I have previously worked with a calendar table that maps out the financial periods to calendar dates, and an exchange rate table that holds spot rates based on currency code, effective date and rate. Finding the relevant rate for a transaction is then a matter of using the calendar and exchange rate tables together to locate the relevant effective rate for the transaction date.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

TomThomson SSC Guru Points: 104773 More actions · Answer 5

mister.magoo (1/28/2015)
I have previously worked with a calendar table that maps out the financial periods to calendar dates, and an exchange rate table that holds spot rates based on currency code, effective date and rate. Finding the relevant rate for a transaction is then a matter of using the calendar and exchange rate tables together to locate the relevant effective rate for the transaction date.

Having just currency and effective date doesn't work unless you are able to get interbank rate. Most people aren't - they have to accept a rate which allows whoever is provding the exchange to take a profit. Last time I looked, shops on the high street here would give me between 1.30 and 1.32 euros (depending which shop) to the pound if I offered UK bank notes, but the banks would give me between 1.25 and 1.28, depending on the bank, and then charge me another 3% for the privilege of using their services. So you end up with effective date, currencies, who offered the rate, and the rate offered if you want to be accurate - so you don't have enough columns for my real world, although it may be adequate for a different situation.

Tom