design question: store products priced in different currencies - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

design question: store products priced in different currencies

Skier88

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

10+ Year Member

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?

topr8

9:29 am on Mar 3, 2011 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

>>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?

Skier88

7:43 pm on Mar 3, 2011 (gmt 0)

10+ Year Member

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.

Skier88

7:57 pm on Mar 3, 2011 (gmt 0)

10+ Year Member

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?