Универсальный каталог

Анатолий · 14 Мар 2007

Универсальный каталог

Фактически вопрос будет, действительно, по универсальному каталогу товаров.

Недавно я просил вас помощи в теме Помогите определиться со структурой БД.
Теперь, не сочтите за наглость =), вновь прошу помощи по этому же проекту.

Я остановился на втором варианте БД: http://larin.in/without-xml.gif
Но теперь встал вопрос, как хранить данные различных типов, например, для некоторый параметров будет достаточно поля типа varchar(255) , для других int(6), а некоторым нужно поле типа TEXT.
В предудущем проекте я создавал несколько таблиц аналогичных таблице mylib_bookvalue они различались только типом поля value. (см. рис. )

Однако, на практике, было очень неудобно осуществлять поиск по такому каталогу.

Как быть в такой ситуации? Не хочется в таблице mylib_bookvalue поле value делать типа TEXT. Уж больно это расточительно, например, хранить количество страниц в книге в поле типа TEXT.

zerkms · 15 Мар 2007

для подобных вещей мы пользуемся следующим набором таблиц:

[sql]
DROP TABLE IF EXISTS `catalogue_catalogue`;

CREATE TABLE `catalogue_catalogue` (
`id` int(11) unsigned NOT NULL auto_increment,
`type_id` int(11) unsigned default NULL,
`name` varchar(255) NOT NULL default '',
`editor` int(11) default NULL,
`created` int(11) default NULL,
`obj_id` int(11) default NULL,
`folder_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `folder_id` (`folder_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

#
# Structure for the `catalogue_catalogue_data` table :
#

DROP TABLE IF EXISTS `catalogue_catalogue_data`;

CREATE TABLE `catalogue_catalogue_data` (
`id` int(11) NOT NULL default '0',
`property_type` int(11) unsigned default NULL,
`text` text,
`char` varchar(255) default NULL,
`int` int(11) default NULL,
`date` datetime default NULL,
`float` float(9,3) default NULL,
UNIQUE KEY `property_type` (`property_type`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

#
# Structure for the `catalogue_catalogue_properties` table :
#

DROP TABLE IF EXISTS `catalogue_catalogue_properties`;

CREATE TABLE `catalogue_catalogue_properties` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` char(255) default NULL,
`title` char(255) default NULL,
`type_id` int(11) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

#
# Structure for the `catalogue_catalogue_properties_types` table :
#

DROP TABLE IF EXISTS `catalogue_catalogue_properties_types`;

CREATE TABLE `catalogue_catalogue_properties_types` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) default NULL,
`title` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

#
# Structure for the `catalogue_catalogue_types` table :
#

DROP TABLE IF EXISTS `catalogue_catalogue_types`;

CREATE TABLE `catalogue_catalogue_types` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` char(255) default NULL,
`title` char(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

#
# Structure for the `catalogue_catalogue_types_props` table :
#

DROP TABLE IF EXISTS `catalogue_catalogue_types_props`;

CREATE TABLE `catalogue_catalogue_types_props` (
`id` int(11) unsigned NOT NULL auto_increment,
`type_id` int(11) unsigned default NULL,
`property_id` int(11) unsigned default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `type_id` (`type_id`,`property_id`),
KEY `property_id` (`property_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

[/sql]

пока его хватает, но в ближайшем будущем думаю придётся добавить ещё парочку

jer · 15 Мар 2007

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

zerkms · 15 Мар 2007

catalogue_catalogue - общие данные
catalogue_catalogue_data - непосредственно данные каталога
catalogue_catalogue_properties - свойства (диагональ, вес, ширина, цвет, ...)
catalogue_catalogue_properties_types - типы данных свойств (определяют имена+типы поле в таблице data)
catalogue_catalogue_types - типы объектов (монитор, клавиатура, пылесос)
catalogue_catalogue_types_props - таблица связей: тип объекта - свойство. по ней определяется какие свойства есть у каждого из типов объектов

думаю из объяснений + имён полей связи сам можешь выстроить

jer · 15 Мар 2007

можно уточнить? /сам бъюсь над очередной итерацией удобного каталога товаров/

1. если catalogue_catalogue.type_id - тип товара,
то catalogue_catalogue.obj_id что такое?

2. цена товара хранится в отдельной таблице или в catalogue_catalogue_data.`float`?

Анатолий · 15 Мар 2007

zerkms
Вопрос то был как хранить различные типы данных: int, float, text, etc.
Или я не понял вашу структуру или она не решает поставленного вопроса.

jer · 15 Мар 2007

CREATE TABLE `catalogue_catalogue_data` (
`id` int( 11 ) NOT NULL default '0',
`property_type` int( 11 ) unsigned default NULL ,
`text` text,
`char` varchar( 255 ) default NULL ,
`int` int( 11 ) default NULL ,
`date` datetime default NULL ,
`float` float( 9, 3 ) default NULL ,
UNIQUE KEY `property_type` ( `property_type` , `id` )
)ENGINE = MYISAM DEFAULT CHARSET = cp1251;

zerkms · 15 Мар 2007

jer
то catalogue_catalogue.obj_id что такое? - это мзз-депендент поле, служебное

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

que_bunt · 15 Мар 2007

zerkms, я когда-то делал каталог по структуре подобной вашей, но при больших обьемах базы (большое количество обьектов + больше количестве свойств обьектов) у меня скрипты выполнялись слишком долго.
немогли бы вы привести пример запроса на выборку например одной позиции со всема свойствами по id?
(можна в личку, можна еще куда-нибудь, чтобы не замусоривать топик).

буду благодарин.

и также появлялся вопрос поиска по динамическому полю, если fulltext решался просто, то поиск по какому-то цифровому свойству не решался, может просто опыта и понимания дела мало.

Анатолий · 15 Мар 2007

Ой, простите, не увидел =)))
Мне вчера товарищ так посоветовал сделать, НО

Доступ к строкам таблицы осуществляется быстрее, если все ее строки имеют одинаковую длинну. Чтобы строки имели фиксированную длину, фиксированную длину должны иметь все столбцы таблицы. Это означает отсутствие в таблице столбцов типа VARCHAR, TEXT, BLOB.
Если необходимо хранить данные этих типов, стоит рассмотреть частичную денормализацию БД и вынести данные столбцы в отдельные таблицы.

Так, что пример хороший, но для большей производительности его стоит чуть доработать.

que_bunt · 15 Мар 2007

zerkms · 15 Мар 2007

[sql]
SELECT * FROM `catalogue_catalogue` `catalogue`
LEFT JOIN `catalogue_catalogue_types` `t` ON `t`.`id` = `catalogue`.`type_id`
LEFT JOIN `catalogue_catalogue_types_props` `tp` ON `tp`.`type_id` = `t`.`id`
LEFT JOIN `catalogue_catalogue_properties` `p` ON `p`.`id` = `tp`.`property_id`
LEFT JOIN `catalogue_catalogue_data` `d` ON (`d`.`property_type` = `tp`.`id`) AND (`d`.`id` = `catalogue`.`id`)
WHERE `catalogue`.`id` = 2
[/sql]

explain:

Код:

+----+-------------+-----------+--------+---------------+---------+---------+--------------------+------+-------+
| id | select_type | table     | type   | possible_keys | key     | key_len | ref                | rows | Extra |
+----+-------------+-----------+--------+---------------+---------+---------+--------------------+------+-------+
|  1 | SIMPLE      | catalogue | const  | PRIMARY       | PRIMARY |       4 | const              |    1 |       |
|  1 | SIMPLE      | t         | const  | PRIMARY       | PRIMARY |       4 | const              |    1 |       |
|  1 | SIMPLE      | tp        | ref    | type_id       | type_id |       5 | const              |    1 |       |
|  1 | SIMPLE      | p         | eq_ref | PRIMARY       | PRIMARY |       4 | mzz.tp.property_id |    1 |       |
|  1 | SIMPLE      | d         | ref    | id            | id      |       9 | const,mzz.tp.id    |    2 |       |
+----+-------------+-----------+--------+---------------+---------+---------+--------------------+------+-------+

-~{}~ 15.03.07 21:05:

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

Анатолий · 15 Мар 2007

zerkms
=) Понял... Спасибо за ответы!!!
А на каком максимальном объеме данных тестировался ваш каталог? Любопытно. Но как я понял, на производительность вы не жалуетесь...

zerkms · 15 Мар 2007

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

jer · 15 Мар 2007

Так, что пример хороший, но для большей производительности его стоит чуть доработать.

есть идеи по доработке?

ты же сам говоришь, что денормализация в несколько таблиц ведет к:

Однако, на практике, было очень неудобно осуществлять поиск по такому каталогу.

Анатолий · 15 Мар 2007

есть идеи по доработке?

Есть. На днях выложу структуру.

ты же сам говоришь, что денормализация в несколько таблиц ведет к:

Однако, на практике, было очень неудобно осуществлять поиск по такому каталогу.

Нажмите для раскрытия...

Там была другая структура денормализации. =)

svetasmirnova · 16 Мар 2007

Доступ к строкам таблицы осуществляется быстрее, если все ее строки имеют одинаковую длинну. Чтобы строки имели фиксированную длину, фиксированную длину должны иметь все столбцы таблицы. Это означает отсутствие в таблице столбцов типа VARCHAR, TEXT, BLOB.

Несколько устаревшая информация. Для новых версий используйте [sql]ROW_FORMAT=fixed[/sql] См. также http://dev.mysql.com/doc/refman/5.0/en/data-size.html

>Однако, на практике, было очень неудобно осуществлять поиск по такому каталогу.
А как проявлялось это неудобство?

dark-demon · 17 Мар 2007

проблема поиска решается организацией отдельного поискового индекса...

que_bunt · 19 Мар 2007

svetasmirnova
например если поле текстовое то фулл-текст поиск осуществляеться легко, но если например поле числовое то невозможно осуществить поиск с условием "WHERE num_dunaminc_field>1.321 AND num_dunaminc_field<2.123"

zerkms · 19 Мар 2007

que_bunt
ты дал этот ответ учитывая что под каждый тип заводится своё поле конкретного типа?

Универсальный каталог

Новичок

TDD infected

...

TDD infected

...

Новичок

...

TDD infected

Новичок

Новичок

Новичок

TDD infected

Новичок

TDD infected

...

Новичок

маленький монстрик

d(^-^)b

Новичок

TDD infected