Pull to refresh

Разворачивание широкой таблицы в столбец (EAV pattern)

SQL *

Задача


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


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

Первое.
Число параметров переменно => храним разные свойства сущности в таблице такого вида:
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
Tags:
Hubs:
Total votes 42: ↑29 and ↓13 +16
Views 26K
Comments 65
Comments Comments 65