There is a table of "Products" of the following structure:

[id, price, price_currency]

And about the following content:

 [1, 100, RUR] [2, 2, USD]

There can be an unlimited amount of currencies, but their quotes are not stored in the database.

Actually the question is how to organize data storage, or work with a similar table so that it is convenient to sort and search using the single currency (for example, USD) as the base currency? In other words, before sampling, you need to somehow bring all prices to a single value in USD currency using an external quote.

There is an idea to supplement the table with the field price_base and store the price in it in the base currency, as well as recalculate this value once a day, but maybe there is a simpler solution?

  • And why not keep quotes in the database, for example, just once a day. Download all quotes faster than using them to update all records in the database, alternately taking them from there and calculating them - Mike
  • I do not want to use cron to update the quotes, and such a table will require complication of SQL as a whole, since it will be necessary to join it to the table of goods and recalculate the cost of it dynamically. - Artur-Mamedbekov
  • one
    And to update all the records in the database once a day as if you want not through cron ... - Mike
  • Even if the task was hard to update the price field in the base unit once a day, I would recommend to first load the quotes into the database. and then, on the basis of them, to update, so many times (if not ten times) faster than to do something with records based on external data - Mike
  • I don’t want to update anything once a day in the database, just for now this is the only solution I've thought of, but I don’t like it either. PS There is no question of optimization at all. - Artur-Mamedbekov 7:55

2 answers 2

There should be a table of currency values ​​in some basic units. At least in rubles, even in tugriks ... and, accordingly, all prices are reduced to this base currency or some particular selected one - in the request. And the table itself is either updated or updated, if you need to store history (then, besides the cost, there will also be a time stamp).

  • Comments are not intended for extended discussion; conversation moved to chat . - PashaPash ♦
  1. stored in the database in the currency in which the price is set, without recalculating
  2. make a table with currency relations and the date of update and update if outdated before searching
  3. in the search to make yes, damn complicated query with several blocks OR (by the number of currencies) in which conditions with coefficients
  4. want faster - to divide the goods into price categories with an index