Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

design question: store products priced in different currencies

1:18 am on Mar 1, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:July 30, 2009
posts: 130
votes: 0

I am designing a website that aggregates information about products, including MSRP. As the site is not limited to one nation's products, MSRPs will be provided in different currencies. I want to be able to sort entries by price, but I also need to store the actual MSRP so that fluctuations in exchange rates do not make prices inaccurate.

I know there are many approaches to this problem, but I don't know enough about database design to know which would be the best for my situation. I am using MySQL and ISAM, and the site could potentially index tens of thousands of products. Additionally, I would like to have as few price related columns in my main product table as possible, since so far I've managed to keep the table devoid of extraneous columns, making displaying the data easier and the entire system easier to expand or modify.

The approach I'm considering now is to store the MSRP and its currency with every product, and sort via a stored procedure. I would then have another table of currencies and their conversion rate to a central currency. This would also let me display prices in any supported currency; however, I am not sure if this approach is as efficient as it should be. And I am unfamiliar with stored procedures, but I think they are applicable here and I have no problem learning something new if it is useful.

If you got this far, thanks for sticking with me. Any suggestions, solutions, or just tips?
9:29 am on Mar 3, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
votes: 29

>>stored procedures

these are queries or series that are saved in the database, it is just a different way of querying the database - personally i use them almost exclusively for everything, but that's a whole other question.

>>multi currency

if i understand you, you are looking to display a product in its original currency and then for reference show it in the user's currency as well?

otherwise your plan on how to do it seems reasonable to me, the MSRp and the currency in the product table and a table with currency conversion rates.
i'd then do the calculation in php or what ever your server language is.

is the site informational? or are you actually selling the products?
7:43 pm on Mar 3, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:July 30, 2009
posts: 130
votes: 0

Thanks for the reply. I looked into stored procedures and you're right, that's another question. The reason I (mistakenly) mentioned them is that I was looking for a way to speed up my main point of concern, sorting by price. If every item only stores its price and the currency it is in, in order to sort by price a query would have to look up each item's currency in another table, convert it to the reference currency, then sort by that value. I only know basic SQL, so I'll have to do a bit of googling to get the query right, but this is the basic idea:
SELECT * FROM product_info ORDER BY msrp*(SELECT rate FROM currencies WHERE name=currency)
With 10000 records (again, not likely, but possible) I don't know if that would be a reasonable amount of work to perform on many of the site's pageloads.

I was actually thinking I would display only the converted MSRP, but now that you mention it it's a much better idea to display both. As you can see this site is far from completion ...

Speaking of which, yes, the website will be purely informational. The idea is a sort of product wiki, with specific and searchable fields and an emphasis on reviews.
7:57 pm on Mar 3, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:July 30, 2009
posts: 130
votes: 0

Did a little more searching, and this should be closer to the right query:
SELECT a.*,b.rate FROM product_info a LEFT JOIN currencies b ON a.currency=b.name ORDER BY a.msrp*b.rate
Do you think this is too inefficient?

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members