Разработка базы данных EAV правильно для исторических данных
вступление
Я читал о базе данных EAV, и большинство недостатков, по-видимому, связано с очень, очень плохими конструкциями EAV или трудностями при создании отчетов из данных.
Обычно, когда вы видите, что люди жалуются на EAV, они используют менее трех таблиц, чтобы попытаться функционально воспроизвести отдельные таблицы + столбцы в RDBMS. Иногда это означает хранение всего от десятичных знаков до строк в одном TEXT столбец значений. EAV также портит средства защиты целостности данных, что может быть очень плохо, если вы не будете осторожны.
Тем не менее, EAV предоставляет простой способ отслеживания исторических данных и позволяет нам перемещать части системы назад и вперед между системами SQL и хранилищем значений ключей.
Что делать, если мы разделяем различные атрибуты сущности на основе их типа. Это позволило бы нам по-прежнему обрабатывать отношения ownTo, Has, HasMany и HasManyThrough в дополнение к правильно проиндексированным значениям, привязанным к конкретным атрибутам и объектам.
Учитывая следующие две базовые сущности
products (price -> decimal, title -> string, desc -> text, etc...)
attributes
options
[...]
int
datetime
string
text
decimal
relation
[id,foreign_key]
users (gender -> options, age -> int, username -> string, etc...)
attributes
options
[...]
int
datetime
string
text
decimal
relation
[id,foreign_key]
Разработка схемы РСУБД
Как все мы знаем, профили пользователей и продукты являются одними из самых разнообразных элементов в мире. Каждая компания обрабатывает их по-разному и имеет различные "столбцы" или "атрибуты" для своих нужд.
Ниже приведено представление о том, как обрабатывать несколько (вложенных и / или реляционных) объектов.
Идея состоит в том, что для каждой сущности есть эта главная таблица атрибутов, которая затем определяет, как найти и интерпретировать эти значения. Это позволяет нам обрабатывать особые случаи, такие как внешние ключи для других объектов и такие вещи, как "опции" или десятичные числа.
entity_type {id, type, // то есть "блог", "пользователь", "продукт" и т. д. creation_at }
entity {
id,
entity_type_id,
created_at
}
attr {
id,
entity_id,
type,
name,
created_at
}
option {
id,
attr_id,
entity_id,
multiple, // multiple values allowed?
name,
created_at
}
attr_option {
id
attr_id,
entity_id,
option_id
option,
created_at
}
attr_int {
attr_id,
entity_id,
int,
created_at
}
attr_relation {
attr_id,
entity_id,
entity_fk_id,
created_at
}
attr_datetime {
attr_id,
entity_id,
datetime,
created_at
}
attr_string {
attr_id,
entity_id,
var_char,
created_at
}
attr_text {
attr_id,
entity_id,
text,
created_at
}
attr_decimal {
attr_id,
entity_id,
decimal,
created_at
}
Такая таблица позволит нам никогда не UPDATE ... так как мы могли бы просто INSERT INTO ... для каждого нового атрибута, который меняет значение и добавляет created_at знать, что является самым последним значением. Это идеально подходит для хранения исторических данных (исключения могут быть сделаны, конечно).
Примеры запросов
Во-первых, что это за "тип" сущности? (пользователь, пост, комментарий и т. д.)
SELECT * FROM entity_type et LEFT JOIN entity e ON e.entity_type_id = et.id WHERE e.id = ?
Далее, каковы атрибуты этого объекта? (ТАБЛИЦА attr)
SELECT * FROM attr WHERE entity_id = ?
Далее, какие значения существуют в атрибутах для этой сущности? (таблицы _###)
SELECT * FROM attr_option, attr_int, attr_relation, attr_text, ... WHERE entity_id = ?
vs
SELECT * FROM attr_option WHERE entity_id = ? if( ! multiple) ORDER BY created_at DESC LIMIT 1
SELECT * FROM attr_int WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1
SELECT * FROM attr_relation WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1
SELECT * FROM attr_text WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1
...
Какие отношения существуют для этой организации?
Предполагая, что у нас есть сущность "post" с идентификатором 34, и мы хотим, чтобы для нее были "комментарии" (entity_type = 2), это может позволить нам получить идентификаторы сущности комментария для сущности продукта:
SELECT * FROM entity AS e
LEFT JOIN attr_relation AS ar ON ar.entity_id = e.id
WHERE ar.entity_id = 34 AND e.entity_type = 2;
Помимо множественных запросов (которые в любом случае необходимы для хранилищ значений ключей), какие проблемы могут возникнуть при таком подходе?
3 ответа
EAV "база данных" [sic] буквально математически представляет собой недокументированное описание в трех экземплярах базы данных и ее метаданных, без каких-либо функций для табулирования отношений, или отношений запроса, или запроса метаданных, или проверки типа, или поддержания целостности, или оптимизации, или совершать транзакции атомарно, или контролировать параллелизм.
Принципы разработки программного обеспечения диктуют, что разумное использование базы данных EAV [sic] полностью состоит из определения соответствующих абстракций (типов, операторов, процессов, интерпретаторов, модулей), воссоздающих функциональные возможности СУБД.
Механический характер сопоставления троек EAV и их значений с (фрагментированным) описанием базы данных позволяет легко это показать.
Перефразируя Гринспена, любой достаточно сложный проект EAV содержит специальную, неофициально заданную, подверженную ошибкам, медленную реализацию половины СУБД.
Я повторяю: EAV - это недокументированное описание в тройках базы данных и ее метаданных без СУБД. Используйте EAV только для тех частей базы данных, где вы продемонстрировали, что решение DDL не может соответствовать требованиям к производительности и что решение EAV может и стоит того.
Вот некоторые проблемы с этим дизайном.
Как бы вы запросили текущее значение всех целочисленных атрибутов данного объекта?
Как бы вы смоделировали атрибут, который должен быть
NOT NULL? То есть убедитесь, что данный атрибут является обязательным для его объекта, и объект не может быть создан без значения для этого атрибута.Как бы вы смоделировали УНИКАЛЬНУЮ колонку? Предположим, вы можете изменить значение атрибута, а затем изменить его обратно на исходное значение.
Как вы поддерживаете внешние ключи, которые ссылаются на сущность с чем-то отличным от целочисленного первичного ключа?
Как вы ограничиваете данный атрибут набором значений в таблице поиска?
Единственный способ решить большинство из них - использовать код приложения. В этом и заключается проблема с EAV: в итоге вы заново изобретаете многие ограничения, которые мы считаем само собой разумеющимися в SQL. Это пример антипаттерна Inner Platform Effect:
Эффект внутренней платформы - это тенденция архитекторов программного обеспечения создавать систему, настраиваемую так, чтобы она стала копией, а зачастую и плохой копией платформы разработки программного обеспечения, которую они используют.
Шестая нормальная форма - это не EAV. В шестой нормальной форме вам нужна отдельная таблица для каждого атрибута, а не для каждого типа данных. Вы используете обычные столбцы с соответствующим именем и типом данных. Хранение этого атрибута в отдельной таблице - это то, что дает вам возможность хранить исторические изменения.
Это означает, что вы все еще не можете моделировать NOT NULL в 6NF, но, по крайней мере, вы можете моделировать UNIQUE а также FOREIGN KEY довольно обычным способом.
"Я читал о базе данных EAV, и большинство недостатков, по-видимому, связано с очень-очень плохими конструкциями EAV или трудностями при создании отчетов из данных".
Трудности генерации отчетов по своей природе и неизбежности связаны с тем, что БД EAV представляет из себя: "Значение атрибута BIRTHDATE для лица XYZ - это..." "Значение атрибута DECEASEDATE для лица XYZ - это..." и т. Д. И т. Д.,
Это НЕ типичная форма, в которой конечные пользователи думают о структурах данных для переноса информации о человеке XYZ (или любой другой), то есть где-то между конечным пользователем и БД, дополнительного преобразования (очень похоже на поворот, хотя не совсем 100). %) является необходимым. Каждое дополнительное преобразование является потенциальным источником ошибок и потери производительности.
"Обычно, когда вы видите, что люди жалуются на EAV, они используют менее трех таблиц, чтобы попытаться воспроизвести функционально отдельные таблицы + столбцы в СУБД. Иногда это означает хранение всего, от десятичных знаков до строк, в одном столбце значений TEXT".
Это только один из недостатков EAV. Ограничения типов на уровне атрибутов становятся более трудными или невозможными для определения. Но есть и другие, кроме этого.
"EAV также портит средства защиты целостности данных, что может быть очень плохо, если вы не будете осторожны".
Это полностью связано со сложностью генерации отчетов, что точно так же, как сложность выражения значимых запросов, что точно так же, как и сложность выражения сценариев, которые представляют собой нарушение какого-то данного правила.
"Тем не менее, EAV обеспечивает простой способ отслеживания исторических данных и позволяет нам перемещать части системы назад и вперед между системами SQL и хранилищами значений ключей".
BS & Baloney. EAV, примененный строго, переместит информацию о времени так же далеко от вещей, к которым это относится, как любой другой "обычный" атрибут. Если вы этого не сделаете, то вы больше не будете применять EAV (строго). Смотрите ответ Билла Карвина: EAV!= 6NF!!!!!!!!! 6NF по-прежнему обладает всей "структурой", которую имеет любая другая "обычная" БД, а EAV (см. Ответ Филиппа и замечание Билла о "внутренней платформе") эффективно удаляет эту структуру из БД.