Skier88 - 1:18 am on Mar 1, 2011 (gmt 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?