Запрос Mysql для одновременной фильтрации двух или более валют продуктов

#mysql #sql

Вопрос:

У меня есть две таблицы с именами продукты и валюты

 CREATE TABLE `products` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR(15) DEFAULT NULL,
  `price` INT DEFAULT NULL,
  `price_currency` VARCHAR(5) DEFAULT NULL,
  PRIMARY KEY  (`id`)
)

CREATE TABLE `currencies` (
  `currency_name` VARCHAR(5) DEFAULT NULL,
  `buy_value` FLOAT DEFAULT NULL,
  `sell_value` FLOAT DEFAULT NULL,
  `modified` DATETIME DEFAULT NULL
)

У меня есть несколько записей в таблице продуктов, которые имеют цены в долларах США, а другие имеют цены в евро. У меня также есть таблица валют, в которой хранятся значения покупок и продаж в долларах США, евро и т.д. Когда посетитель вводит цены в долларах США для фильтрации всех товаров с помощью формы поиска, запрос должен одновременно проверять товары, цены на которые указаны в евро.

Итак, как я могу это сделать?

Ответ №1:

Я бы изменил структуру таблицы валют. Вместо того, чтобы иметь значение покупки и продажи, у меня было бы что-то вроде

СОЗДАТЬ ТАБЛИЦУ currencies ( source_currency VARCHAR(5) NOT NULL, dest_currency VARCHAR(5) NOT NULL, value FLOAT NOT NULL, modified DATETIME ПО УМОЛЧАНИЮ NULL )

И добавьте набор поддельных записей для случаев, когда пользователь хочет использовать ту же валюту (т.е. Вставить (EUR, EUR, 1, 1//1/1970), ( USD, USD, 1/1/1970)

Затем это становится простым (хотя и медленным) запросом

 SELECT products.* FROM products INNER JOIN currency ON price_currency = source_currency WHERE price_min <= price * value AND price * value <= price_max AND dest_currency = desired_currency.

Поскольку умножение не может быть оптимизировано, это будет полное сканирование таблицы.
Другой альтернативой является денормализация таблицы продуктов, чтобы иметь цену во всех возможных валютах, поэтому при обновлении валют вы пересчитываете цены. Тогда поиск становится тривиальным (полностью индексируется), И вы можете устанавливать дифференциальные цены (а-ля apple), которые вы обновляете вручную.
Кроме того, если вы используете http://en.wikipedia.org/wiki/ISO_4217 вам нужен только VARCHAR(3), а не 5