Skier88 - 7:43 pm on Mar 3, 2011 (gmt 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:
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.
SELECT * FROM product_info ORDER BY msrp*(SELECT rate FROM currencies WHERE name=currency)
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.