Storing Money Datatype (Value and Type in same column)

Question

Post reply

Storing Money Datatype (Value and Type in same column)

nileshsane

Default port

Points: 1465
More actions
June 9, 2006 at 1:51 am

#60502

I have a requirement of storing Monetary data, and I am considering the decimal and money data types. The requirement is to store the data along with the type of currency it represents, since the application would be used globally and the users would like to store the currency values as per the currency in which they are operating. So a user from the US would want to store it as $500 while someone from the UK would want to store it as PoundSign500.
So, not only is the value of currency important to me, even the type of currency is important. I could do this by using the decimal type to store the value and add a column, which would store the type of that currency. Something like
CurrVal Decimal
CurrType Char(2).
I'm curious to know if SQL Server will provide me with an implementation to store the currency value and type in the same column, and even if I do it, how do i go about retrieving and updating such rows.
What I hear I forget, what I see I remember, what I do I understand

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

In SQL2005, you could roll a UDT and include a column of that type in your table.

Under SQL2000, the only way to get both in a column is with a char/varchar, which is kind of useless for money.

The two-column method you proposed above will serve your needs nicely.

Stretching things a little too far follows:

A little bitmasking on the currency type would allow you to also add several calculated columns for the different types (values would only appear in the column for their specific currency, other columns would be zero):

CurrencyType:

1 = USD

2 = CDN

4 = JPY

8 = EUR

16 = RM

column definitions:

CurType int (insert currency type here)

CurVal smallmoney (insert cash value here)

US Dollars = CASE WHEN (CurType & 1) = 1 THEN 1 ELSE 0 END * CurVal

CA Dollars = CASE WHEN (CurType & 2) = 2 THEN 1 ELSE 0 END * CurVal

JP Yen = CASE WHEN (CurType & 4) = 4 THEN 1 ELSE 0 END * CurVal

Euros = CASE WHEN (CurType & 8) = 8 THEN 1 ELSE 0 END * CurVal

Ringitt = CASE WHEN (CurType & 16) = 16 THEN 1 ELSE 0 END * CurVal

-Eddie

Eddie Wuerch
MCM: SQL

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

I would not recommend storing multiple types in one field. Normally the application retrieves the locale of the computer and based on this locale settings modifies data for input or display. The actual money are stored as one type, US Dollars for example.

Regards,Yelena Varsha

David McFarland SSChampion Points: 11815 More actions · Answer 3

One of the nice benefits of using two columns is if you store the currency types in a separate table, and store only the key from that table with your actual currency amounts, you get added flexibility. If a country changes their currency (which does happen), or if someone later decides that you need to do currency conversions, etc.

nileshsane Default port Points: 1465 More actions · Answer 4

Thanks for the replies guys. We are contemplating on going ahead with a two column approach, and unless someone(within the team) comes up with an idea, other than the exciting ones discussed on here, I believe a two column approach will suffice for now.

I'll post the final solution on here once we decide on it. Thanks again.

What I hear I forget, what I see I remember, what I do I understand