Как хранить свойства товаров?

GeT · 24 Фев 2005

Как хранить свойства товаров?

Вот возникла такая проблема. Нужно сделать, чтобы у товаров было неограниченое количество характеристик. Причем чтобы можно было делать выборку/сортировку по этим характеристикам и делать вывод товаров таблицей вместе с характеристиками.
Сначала я сделал вот так:
cat_items (item_id || cat_id || блаблабла) - таблица где хранятся товары
properties (id || prop_name || default_value) - таблица где хранятся свойства товаров.
item_properties (item_id || prop_id || value) - таблица где хранятся значения свойств.

Вот, с добавлением/удалением/выводом все впорядке, а вот с сортировкой проблема.
Есть ли другой способ организации БД такого рода? Да, я уже в поиске видел подобные темы, но ни в одной нету нужного варианта.

Кром · 24 Фев 2005

Схема нормальная. По идее не должно там быть проблем при сортировке.

GeT · 24 Фев 2005

Ну смотри...

[SQL]
SELECT *
FROM cat_items
JOIN item_properties ON cat_items.id = item_properties.item_id
WHERE catid =56
[/SQL]
Вот запрос, достаем все товары и их свойства, где id каталога равен 56.
Я хочу отсортировать, скажем, по свойству, у которого id равен 6. Как это сделать?

kas · 25 Фев 2005

Вопрос сразу возник по этому топику:
а как хранятся сами значения свойств товаров?
ведь свойства могут быть различных типов (текст, число). Заранее например не известно, пользователь например сам определяет какой тип характеристики.
Если например есть для товара свойство description и оно типа TEXT, потому что пользователь туда пишет текста на страницу А4. и тут же например свойство диагональ телевизора, число целое.
А если например надо найти все товары у которых диагональ больше X например? это mysql своими средствами делает пробразование текста в чило и потом делает уже поиск, а возможно ли как-то изменить эту структуру чтобы ускорить время выполнения запроса?

GeT · 25 Фев 2005

kas
у меня для этого в таблице properties есть поле "prop_type"
Оно принимает значения: INT, FLOAT, TEXT, IMG, BOOL.
А в item_properties поле value текстовое. Но MySQL сам делает преобразование типов при запросе.
Скажем, для FLOAT я бы писал ORDER by (value+0.0) и он бы сортировал по float.
Но я все равно не могу составить запрос чтобы вывести все товары с сортировкой по одному из параметров...

-~{}~ 25.02.05 16:42:

Так что, никто не знает или фсем влом ответить? =(

-~{}~ 25.02.05 19:29:

Всем спасибо за помощь =) Я сам въехал.
Если кому интересно, рассказываю:

Нужно делать это через временную таблицу. В ней поля - id и и по 1 полю для каждой из характеристик категории. Записываем туда все данные, которые нужны и сортируем по нужному полю а потом ДРОПаем ее.
Не уверен, что это оптимальный вариант, но другого выхода я просто не вижу =)

chira · 25 Фев 2005

а если так:

Код:

SELECT * 
FROM cat_items
JOIN item_properties i ON cat_items.id = i.item_id
JOIN item_properties ordi ON cat_items.id = ordi.item_id AND ordi.prop_id = 6
WHERE catid = 56
ORDER BY ordi.value, cat_items.item_id

GeT · 25 Фев 2005

chira
Хм, вроде работает. Хотя надо тестить, я тока 1 раз прогнал запрос.
Спасибо большое за помощь =)

SSSerj · 28 Фев 2005

Возникли следующие мысли по данному топику:

kas, продолжу Вашу мысль...
В общем случае, каждая характеристика имеет свой физический тип. Наиболее распространенными типами являются:
1) строка (не более 255);
2) дата;
3) целое;
4) логическое (Y/N);
5) вещественное;
6) тектовое (не более 65536)

Каждому физическому типу, может соответствовать несколько товарных типов: вещественному - вес, литры...., дате - дата изготовления товара, дата появления товара на рынке...и т.д. и т.п.

Решение 2:

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

Решение 3:

Другое решение - ненормализованное - добавить столбцы требуемых физических типов в таблицу item_properties (для даннго примера, физ. типы с 1-5)

Данные рассуждения были приведены из расчета на то, что по каждому физическому типу может быть осуществлен поиск.

Думаем...рассуждаем... комментируем....

GeT · 28 Фев 2005

SSSerj
ЧЕМ тебя не устраивает мой вариант решения? Сделать один лишни столбец в таблице properties, который будет обозначать тип характеристики (текстовый, целый, вещественный, картинка в моем случае). Вот и все. "тип" нужен только для сортировки - больше он НИГДЕ не применяется! Зачем извращаться с новыми столбцами и т.п.?

SSSerj · 28 Фев 2005

GeT, Ваш вариант - тоже хорошее решение. Решение 2 и 3 также предусматривает типизацию, предложенную Вами.

В Вашем случае, возможно "это" и не нужно.

У меня, скорее всего, вопрос обращается к ПУБЛИКЕ, но в рамках Вашей

-~{}~ 28.02.05 19:01:

...тематики

Кто-нибудь так делает?

З.Ы. Больше всего интересует вопрос поиска и сравнения информации (товаров) по характеристикам.

kas · 28 Фев 2005

Во, пошло обсуждение этой проблемы...
да, существует способы хранения различных типов данных, но их много и самое пока что быстрое это как раз хранить значения в текстовом поле, но уж очень жалко разбазаривать объем который наверняка в конечном счете скажется на производительности, да и если математику сильно использовать то мускл просядет на преобразованиях если в текстовом поле хранить целые например.

Если под значения в соответствии с типом делать по таблице то время запроса будет теряться не на преобразование а на join, это я думаю тоже непримелимо

динамически делать alter table тоже неправильно, потому что свойств для различных товаров можеть быть немеряный и если потом потребуется скажем получить для всех групп товаров по первому товару с полным набором характеристик, то количество полей будет немеряным

Поэтому кроме как первого варанта решений не видно, было бы тоже услышать, есть ли какие-то решения данного вопроса

SSSerj · 1 Мар 2005

Для тех кому интересно:
1) ddl.zip - содержит SQL для создания рабочих таблиц;
2) data.zip - содержит SQL (INSERT - команды) cо "случайными" данными (каждый, верней почти каждый, товар содержит по 5 характеристик, см. таблицу property_list)
3) query.zip - запросы по выборке данных.

Собственно ссылка - http://ssserj.avtohome.ru/shop/

Постараюсь не удалять данные, находящиеся по текущей ссылке

Еще раз повторюсь - интересны все темы, так или иначе относящиеся к поиску и хранению информации.

GeT · 1 Мар 2005

SSSerj
Я как свой вариант доделаю - выложу тоже.
Щас пошел смотреть твое =)

SSSerj · 2 Мар 2005

Да...не написал самого интересного.

А теперь попробуйте найти те товары (таблица goods), характеристики которого удовлетворяют следующему условию: Количество деталей [property_list(id_property)=5] больше 150 шт и Дата поступления товара [property_list(id_property)=3] не раньше чем ноябрь 1985 г.

-~{}~ 02.03.05 12:23:

Решение найдено - помогли временные таблицы.
Таким образом, для данного поиска нам необходимо две временные таблицы:

1) Таблица, хранящая данные только о <датах поступления товара>:

Код:

CREATE TEMPORARY TABLE new_date_tmp
 (`id` mediumint(9) unsigned NOT NULL auto_increment,
 `id_goods` smallint(5) unsigned NOT NULL default '0',
 `date_property` date default NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `date_property` (`date_property`), //вспомогательный ключ 1.1
 KEY `d_index_date` (`id_goods`,`date_property`) // вспомогательный ключ 1.2
 ) ENGINE=MyISAM DEFAULT CHARSET=cp1251
 SELECT
 `info_goods`.`id_goods`,
 CAST(`info_goods`.`property_value` AS DATE) date_property
 FROM `info_goods`
 WHERE `info_goods`.`property_list_id_property_list`=3;

2) Таблица, хранящая данные только о <количестве деталей>:

Код:

CREATE TEMPORARY TABLE new_int_tmp
 (`id` mediumint(9) unsigned NOT NULL auto_increment,
 `id_goods` smallint(5) unsigned NOT NULL default '0',
 `int_property` smallint(6) unsigned default NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `int_property` (`int_property`), //вспомагательный ключ 2.1
 KEY `d_index_int` (`id_goods`,`int_property`) //вспомогательный ключ 2.2
 ) ENGINE=MyISAM DEFAULT CHARSET=cp1251
 SELECT
 `info_goods`.`id_goods`,
 CAST(`info_goods`.`property_value` AS UNSIGNED) int_property
 FROM `info_goods`
 WHERE `info_goods`.`property_list_id_property_list`=5;

Запрос, предназначенный для поиска товаров будет выглядить так:

Код:

SELECT `goods`.*, A1.`date_property`, A2.`int_property`
 FROM goods
 INNER JOIN `new_int_tmp` A2 ON A2.id_goods = goods.id_goods and A2.`int_property`>150
 INNER JOIN `new_date_tmp` A1 ON A1.id_goods = goods.id_goods and A1.`date_property`>'1985-11-01'
 WHERE A1.id_goods = A2.id_goods
 ORDER BY A1.`date_property`;

Хочу отметить, что огромное значение для скорости поиска товаров, имеют индексы (вспомогательные ключи 1.1. 1.2. 2.1. 2.2) во временных таблицах. Приведу некоторые данные по этому поводу:
1) Ключи 1.1, 1.2, 2.1, 2.2 созданы - время выполения запроса 0,17 сек.
2) Только 1.1, 2.1 созданы - время 3.97 сек.
3) Вообще нет ключей - время 162 сек.

Конечно, приведенная выше статистика будет отличаться от реальной. Я привел ее только в качестве образовательных целей.

chira · 2 Мар 2005

SSSerj
чем твой вариант отличается от такого?

Код:

SELECT `goods`.*, A1.`date_property`, A2.`int_property`
 FROM goods
 INNER JOIN `info_goods` A2 ON A2.id_goods = goods.id_goods and A2.property_list_id_property_list=5 and A2.`property_value`>150
 INNER JOIN `info_goods` A1 ON A1.id_goods = goods.id_goods and A1.property_list_id_property_list=3 and A1.`property_value`>'1985-11-01'
 WHERE A1.id_goods = A2.id_goods
 ORDER BY A1.`date_property`;

зачем временные таблицы?

SSSerj · 2 Мар 2005

Ура...санитары проснулись!!!

Ну во-первых...таблица info_goods не имеет ствойств: `date_property` и `int_property`, поэтому Ваш вариант не работает.

Во-вторых, таблица info_goods имеет только одно текстовое поле (тип TEXT), cодержащее значение свойства (характеристики) товара - см.
http://ssserj.avtohome.ru/shop/ddl.zip

-~{}~ 02.03.05 14:19:

Число решений моего варианта - 1035

Код:

SELECT count(goods.`id_goods`) as counts_g
FROM goods
INNER JOIN `new_int_tmp` A2 ON A2.id_goods = goods.id_goods and A2.`int_property`>150
INNER JOIN `new_date_tmp` A1 ON A1.id_goods = goods.id_goods and A1.`date_property`>'1985-11-01'
WHERE A1.id_goods = A2.id_goods
ORDER BY A1.`date_property`;

Ваш Вариант - 1089:

Код:

SELECT count(goods.`id_goods`) as counts_g
FROM goods
INNER JOIN `info_goods` A2 ON A2.id_goods = goods.id_goods and A2.property_list_id_property_list=5 and A2.`property_value`>150
 INNER JOIN `info_goods` A1 ON A1.id_goods = goods.id_goods and A1.property_list_id_property_list=3 and A1.`property_value`>'1985-11-01'
 WHERE A1.id_goods = A2.id_goods
 ORDER BY A1.`property_value`;

Где правда - подскажите!?

-~{}~ 02.03.05 16:15:

А правда есть! Надо правильно хранить дату.
Запрос позволяющий исправить ситуацию, то есть получить одинаковые результаты:

Код:

UPDATE `info_goods`
 SET `info_goods`.`property_value` = CONCAT(DATE_FORMAT(`info_goods`.`property_value`,'%Y'), '-', DATE_FORMAT(`info_goods`.`property_value`,'%m'), '-', DATE_FORMAT(`info_goods`.`property_value`,'%d'))
 WHERE `info_goods`.`property_list_id_property_list` = 3;

Теперь варинт, предложенный chira, такой же... Но есть некоторые тонкие моменты, уведеть которые можно используя EXPLAIN для запроса SELECT (я слегка изменил условия поиска):

С применением временных таблиц:

Код:

EXPLAIN SELECT A2.`int_property`, A1.`date_property`
FROM goods
INNER JOIN `new_int_tmp` A2 ON A2.id_goods = goods.id_goods and A2.`int_property`>300
INNER JOIN `new_date_tmp` A1 ON A1.id_goods = goods.id_goods and A1.`date_property`>'1999-12-01'
WHERE A1.id_goods = A2.id_goods
ORDER BY A1.`date_property`

Просмотрено всего 10*1*1 строк

Вариант chira - без временных таблиц:

Код:

EXPLAIN SELECT A2.`property_value` as p1, A1.`property_value` as p2
FROM goods
INNER JOIN `info_goods` A2 ON A2.id_goods = goods.id_goods and A2.property_list_id_property_list=5 and A2.`property_value`>300
INNER JOIN `info_goods` A1 ON A1.id_goods = goods.id_goods and A1.property_list_id_property_list=3 and A1.`property_value`>'1999-12-01'
WHERE A1.id_goods = A2.id_goods
ORDER BY A2.`property_value`;

Просмотрено всего 7230*5*1 = 36150 строк....

...или 7*5*1 = 35 строк, при условии добавления индекса для таблицы `info_goods`, где индексное поле имеет значение - KEY `property_list__value`(`property_list_id_property_list`,`property_value`(10))

Напрашивается вывод....

chira · 2 Мар 2005

Ну во-первых...таблица info_goods не имеет ствойств: `date_property` и `int_property`, поэтому Ваш вариант не работает.

придирки, поменяй на property_value где надо,

таблица info_goods имеет только одно текстовое поле (тип TEXT),

это я не смотрел, по моему в большинстве случаев хватит VARCHAR(255)
это может иметь отношение к созданию составного индекса.

Число решений моего варианта - 1035

.....

Ваш Вариант - 1089:

перевод типов можно поставить в условие:
INNER JOIN `info_goods` A2 ON A2.id_goods = goods.id_goods and A2.property_list_id_property_list=5 and CAST(A2.`property_value` AS UNSIGNED) >150
INNER JOIN `info_goods` A1 ON A1.id_goods = goods.id_goods and A1.property_list_id_property_list=3 and CAST(A1.`property_value`AS DATE) >'1985-11-01'

Просмотрено всего 10*1*1 строк

....

Просмотрено всего 7230*5*1 = 36150 строк....

почему-то здесь благополучно забыто создание временных таблиц?

-~{}~ 02.03.05 16:36:

Напрашивается вывод....

какой?

SSSerj · 2 Мар 2005

chira
придирки, поменяй на property_value где надо,

Никаких придирок, данное решение (т.е. запрос) можно понять по-разному (много людей - много мнений). Но Мы то друг-друга поняли

chira
почему-то здесь благополучно забыто создание временных таблиц?

Это сделано сознательно дабы увидеть разницу в результатах. И как можно сократить эту разницу до минимума, добавив еще индекс `property_list__value`(`property_list_id_property_list`,`property_value`(10))) в таблицу `info_goods`.

chira
какой?

Задача имеет "одинаковое решение" для разных логических подходов. И тот и другой вариант меня устраивает.

А вот поиграться и посмотреть как влияет на время поиска количество параметров (количество характеристик товара, по которому осуществляется поиск, в нашем случае было 2) на вариант с временными таблицами и без них - вот это интересно.

-~{}~ 02.03.05 18:52:

Короче говоря, нафиг эти временные таблицы!

chira · 2 Мар 2005

выводы:
1. данные разных типов хранить в разных полях, что бы не делать преобразований типов :
- создать несколько полей value (value_dat DATE, value_int INT, value_dec DECIMAL(10,4), value_chr VARCHAR(255))
2. для моего случая проверить работу (EXPLAIN) c индексами в таблице info_goods
- составной: (id_goods,property_list_id_property_list)
- индексы для полей value_...

SSSerj · 3 Мар 2005

Интересные результаты

Исходные данные:
10000 товаров по 5 характеристик для каждого товара.

Вариант1 - http://ssserj.avtohome.ru/shop/var1/
Все типы свойств товара храняться в одном текстовом поле (см. схему таблицы по ссылке) таблицы info_goods.

Вариант2 - http://ssserj.avtohome.ru/shop/var2/
Все типы свойств товара храняться в разных полях, соответствующих типов (см. схему таблицы по ссылке) таблицы info_goods. (предложение chira)

Условие поиска: Найти все товары, количество деталей которых более 300 шт, Дата поступления не ранее 1997-12-01 и весом более 400 кг.

Запрос для Варианта №1:

Код:

SELECT DISTINCT
A2.`property_value` as Pr2, A1.`property_value` as Pr1, A3.`property_value` as Pr3
FROM goods
INNER JOIN `info_goods` A2 ON A2.id_goods = goods.id_goods
and A2.property_list_id_property_list=5 
and A2.`property_value`>300
INNER JOIN `info_goods` A1 ON A1.id_goods = goods.id_goods
and A1.property_list_id_property_list=3
and A1.`property_value`>'1997-12-01'
INNER JOIN `info_goods` A3 ON A3.id_goods = goods.id_goods
and A3.property_list_id_property_list=4 
and A3.`property_value`>400.00
WHERE A1.id_goods = A2.id_goods and A1.id_goods = A3.id_goods
ORDER BY A1.`property_value`;

Запрос для Варианта №2:

Код:

SELECT DISTINCT
A2.`value_int` as Pr2, A1.`value_dat` as Pr1, A3.`value_dec` as Pr3
FROM goods
INNER JOIN `info_goods` A2 ON A2.id_goods = goods.id_goods
and A2.property_list_id_property_list=5
and A2.`value_int`>300
INNER JOIN `info_goods` A1 ON A1.id_goods = goods.id_goods
and A1.property_list_id_property_list=3
and A1.`value_dat`>'1997-12-01'
INNER JOIN `info_goods` A3 ON A3.id_goods = goods.id_goods
and A3.property_list_id_property_list=4
and A3.`value_dec`>400.00
WHERE A1.id_goods = A2.id_goods and A1.id_goods = A3.id_goods
ORDER BY Pr1;

Explain для Варианта№1:

Explain для Варианта№2:

Принятые имена файлов:
1. ddl.sql - команды SQL для создания соответствующих таблиц
2. data.sql - информационные данные таблиц
3. search.sql - запросы поиска информации

4. explain-before-optimiz.jpg - explain результата запроса search.sql для данных, которые были внесены после создания таблицы info_goods (как-будто 10000 "операторов БД" последовательно вносили информацию, а затем также последовательно попорядку ее сохранили)

5. explain-before-optimiz.jpg - explain результата запроса search.sql после оптимизации п.4

6. explain-after-drop-dump.jpg - explain результата запроса search.sql после полного удаления БД, дампирования данных п. 2 (Эквивалентно тому, что "администратор БД" заново восстановил таблицу)

7. explain-after-drop-dump-optimiz.jpg - explain результата запроса search.sql после оптимизации п.6

Резюме: Вариант 2 - лучший из рассмотренных.

Как хранить свойства товаров?

Новичок

Новичок

Новичок

kas

Guest

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

kas

Guest

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок