Задача
Есть сущность, которая характеризуется огромным и часто переменным числом параметров. Задача хранить эти сущности да еще и так чтоб поиск тоже можно было вести желательно еще и с построением индекса.
Сразу скажу, что не знаю как эта задача решается эталонно, но решить ее нужно было быстро и потому я подумав сделал то решение о котором буду рассказывать.
Первое.
Число параметров переменно => храним разные свойства сущности в таблице такого вида:
prop_description
|(int)id |(enum: bool,int,float,string)prop_type|(varchar)prop_name|
Теперь рассматриваем сущность и стараемся выделить первичные (наиболее используемые в поиске) и вторичные свойства сущности, по первичным свойствам создаем таблицу:
objects
|id| meta data — все первичные сущности|
А как хранить все те 10, 20, 300 вторичных характеристик?
Изначально я задал разделение по типам потому что полагал, что хранить число в строке и вести по ним поиск — кощунственно, потому создаем 3 таблицы.
int_properties, float_properties, string_properties,
(int)object_id — внешний на таблицу объектов primary key
(int)prop_id — внешний на таблицу свойств primary key
value — тип соответствует таблице.
Полагаю, что вы поняли что я имею в виду?
Так я при сохранении объекта получу одну запись в таблицу objects и столько записей в int_properties и float_properties и string_properties, сколько у меня свойств такого объекта, но не более чем число строк в prop_description.
Такое хранение данных называется 3.5 нормальная форма #
Думаю, вопрос того как записать вашу сущность в такое представление вы решите сами и тут нет большой проблеммы: цикл по данным, выбор таблицы, запись.
Намного интереснее проблема поиска, понятно, что чтоб найти что-то в такой структуре нужно таблицы как-то перемножить, весь вопрос в том как? На самом деле не так трудно.
Дальше примеры пишу на PHP ибо его синтаксис наиболее наглядный.
Пишем простенький конструктор SQL запросов:
/**
* положим у нас есть массив $prop_descr[$properties_name] = array('id'=>$prop_id, 'type'=>$prop_type)
* как правило, это предположение может быть выполенно ибо таблица, описывающая свойства объектов мала и часто может быть закешированна
*/
$selectToken=array();
$selectQuery=' FROM objects';
foreach ($data as $properties_name=>$value){
$prop_id=$prop_descr[$properties_name]['id'];
$prop_type=$prop_descr[$properties_name]['type'];
$tableName='t_'.$properties_name;
$selectToken[]=$tableName .'.`value` as '.$properties_name;
// иногда тут нужно вставить LEFT
$selectQuery .= 'INNER JOIN `'. $prop_type .'_properties` AS `'. $tableName .'`
ON (`objects`.`id` = `'. $tableName .'`.`object_id`
AND `' .$tableName. '`.`prop_id ` = '. $prop_id .')';
}
$selectQuery = 'SELECT '.implode(', ', $selectToken).' '.$selectQuery;
PS В коде умышленно нет никаких проверок ибо к логике они не имеют отношения!
Так мы получим результат, развернутый в строки, конечно, чтобы вести поиск по такой результирующей таблице нужно еще и в WHERE писать $tableName.`value, но этого не трудно добиться, заведя массив соответствий: array($properties_name = >$tableName .'.`value`);
Плюсы:
— действительно просто добавлять и удалять характеристики объектов (тоесть проще обслуживать).
— поиск относительно быстрый (уж точно быстрее побитовых масок) ибо используются индексы (primary key)
— таблицы удивительно нормированны и просто нарисовать, объяснить, представить логику.
— еще # от kmmbvnr
Минусы:
— это немного медленне, чем просто одна мега широкая таблица на заранее известном запросе.
— SQL не так читаем (но оптимизатор его хавает тока в путь).
UPD благодарю Joshua за название
en.wikipedia.org/wiki/Entity-Attribute-Value_model
