Задача

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


Сразу скажу, что не знаю как эта задача решается эталонно, но решить ее нужно было быстро и потому я подумав сделал то решение о котором буду рассказывать.

Первое.
Число параметров переменно => храним разные свойства сущности в таблице такого вида:
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 не так читаем (но оптимизатор его хавает тока в путь).