Как хранить метаданные на столбцах
Допустим, вы собираете инсайдерскую информацию о предстоящих выпусках фильмов о супергероях, и ваш основной стол фильма выглядит примерно так:
Таблица 1
Title Director Leading Male Leading Female Villain
--------------------------------------------------------------------------
Green Lantern Kubrick Robert Redford Miley Cyrus Hugh Grant
The Tick Mel Gibson Kevin Sorbo Linda Hunt Anthony Hopkins
В целом, это должно работать очень хорошо и обеспечивать очень простые запросы, а также сравнение между строками.
Тем не менее, вы хотите отслеживать источник каждого факта данных, а также имя журналиста, который обнаружил этот факт. Это, кажется, предлагает какую-то таблицу EAV, подобную этой:
Таблица 2
Movie Attribute Value Source Journalist
----------------------------------------------------------------------------------
Green Lantern Director Kubrick CHUD Sarah
Green Lantern Leading Male Robert Redford CHUD James
Green Lantern Leading Female Miley Cyrus Dark Horizons James
Green Lantern Villain Hugh Grant CHUD Sarah
The Tick Director Mel Gibson Yahoo Cameron
...
Который, хотя он легко захватывает метаданные, которые мы хотели, делает запросы сложнее. Требуется немного больше, чтобы просто получить все основные данные одного фильма. Точнее, здесь вам нужно разобраться с четырьмя строками, чтобы получить четыре важных лакомых кусочка информации о Зеленом фонаре, тогда как в таблице 1 это одна, красиво инкапсулированная строка.
Итак, мой вопрос в свете описанных мною сложностей, и, поскольку я знаю, что в целом таблицы EAV следует избегать, является ли EAV по-прежнему лучшим решением? Похоже, что это единственный разумный способ представления этих данных. Единственная альтернатива, которую я вижу, состоит в том, чтобы использовать таблицу 1 в сочетании с другой, которая содержит только метаданные:
Таблица 3
Movie Attribute Source Journalist
----------------------------------------------------------------------------------
Green Lantern Director CHUD Sarah
Green Lantern Leading Male CHUD James
Green Lantern Leading Female Dark Horizons James
Green Lantern Villain CHUD Sarah
The Tick Director Yahoo Cameron
...
Но это очень опасно, потому что если кто-то изменит имя столбца в таблице 1, например, "Злодей" на "Основной злодей", строка в таблице 3 все равно будет просто сказать "Злодей", и, таким образом, связанные данные, к сожалению, будут отделены. Это могло бы помочь, если бы столбец "атрибута" был связан с другой таблицей, которая служила перечислением столбцов таблицы 1. Конечно, администратор БД будет отвечать за поддержание этой таблицы перечисления, чтобы она соответствовала фактическим столбцам таблицы 1. И на самом деле можно улучшить это еще больше, вместо того чтобы создавать таблицу перечисления вручную, используйте системное представление в SQL Server, в котором хранятся имена столбцов в таблице 1. Хотя я не уверен, что вы можете иметь отношения, которые включают Системные представления.
Что ты предлагаешь? EAV единственный путь?
А что, если это был только один столбец метаданных (просто "Источник" без "Журналист") - все равно необходимо идти по маршруту EAV? У вас могут быть столбцы "Директор", "Директор_источник", "Ведущий мужчина", "Ведущий мужчина_источник" и т. Д., Но это становится очень быстро. Есть ли какое-то лучшее решение, о котором я не думаю?
Если я не уточнил какой-либо пункт, пожалуйста, прокомментируйте, и я добавлю больше по мере необходимости. О да, и данные фильма, которые я использовал, сфабрикованы:)
Изменить: для краткого изложения моего основного вопроса, я хотел бы иметь простоту и истинный дизайн СУБД таблицы 1, которая действительно хорошо описывает запись фильма, при этом сохраняя метаданные атрибутов безопасным и доступным способом. Это возможно? Или EAV единственный путь?
Редактировать 2: Проведя еще несколько веб-исследований, мне еще предстоит найти обсуждение EAV, которое было сосредоточено вокруг желания хранить метаданные в столбцах. Основная причина, приводимая для реализации EAV, - это почти всегда динамические и непредсказуемые столбцы, что не так в моем примере. В моем примере всегда присутствуют четыре одинаковые колонки: директор, ведущий мужчина, ведущая женщина, злодей. Однако я хочу хранить определенные факты (источник и журналист) о каждом столбце для каждой строки. EAV будет способствовать этому, но я бы хотел не прибегать к этому.
Обновить
Используя схему Таблицы 2, за исключением переименования столбца "Фильм" в "Имя" и вызова всей таблицы "Фильм", в SQL Server 2008 выполняется операция поворота для возврата в Таблицу 1:
SELECT Name, [Director], [Leading Male], [Leading Female], [Villain]
FROM (Select Name, Attribute, Value FROM Movie) as src
PIVOT
(
Max(Value)
FOR Attribute IN ([Director], [Leading Male], [Leading Female], [Villain])
) AS PivotTable
9 ответов
Вы можете изменить то, что вы считаете значением факта в своем проекте... кажется, что факт в вашей модели данных может быть выражен в виде следующего N-кортежа:
Movie | FactType | FactValue | FactSource | FactJournalist
Следующие структуры таблиц должны поддерживать модель данных, которую вы хотите, и могут относительно легко быть проиндексированы и объединены. Вы также можете создать представление, которое выводит только значение факта и тип факта, так что вы можете создать следующую перспективу:
MovieID | Movie Name | Director | LeadingMale | LeadingFemale | PrimaryVillain | etc
Интересно, что вы могли бы считать это логическим продолжением полного применения модели EAV к данным и разложения отдельного фильма (с его интуитивным указанием режиссера, ведущего, злодея и т. Д.) В сводную структуру, где атрибуты фокусируются на источнике информации вместо.
Преимущества предлагаемой модели данных:
- оно хорошо нормализовано (хотя для полноты следует, вероятно, нормализовать поле FactType в справочную таблицу)
- можно создать представление, эффективно превращающее типы фактов в табличную структуру
- он является относительно расширяемым и позволяет базе данных обеспечивать ссылочную целостность и (при желании) ограничения количества элементов
- таблица MovieFact может быть разделена на подклассы для поддержки различных типов фактов фильма, а не только тех, которые являются простым текстовым полем
- простые запросы к данным относительно эффективны
Некоторые из недостатков модели данных:
- Сложные (условные) запросы сложнее (но не невозможно) написать (например, найти все фильмы, где Режиссер - А, а Ведущий - Б, и т. Д.)
- Модель несколько менее очевидна, чем более традиционный подход или метод, включающий структуры EAV
- вставки и обновления немного сложнее, потому что обновление нескольких фактов требует обновления нескольких строк, а не нескольких столбцов
У меня есть данные Movie на уровень выше, чтобы нормализовать структуру, и вы можете вставить название фильма в структуру MovieFact для согласованности (поскольку для некоторых фильмов я могу представить, что даже тогда name - это то, для чего вы можете отслеживать информацию об источнике),
Table Movie
========================
MovieID NUMBER, PrimaryKey
MovieName VARCHAR
Table MovieFact
========================
MovieID NUMBER, PrimaryKeyCol1
FactType VARCHAR, PrimaryKeyCol2
FactValue VARCHAR
FactSource VARCHAR
FactJournalist VARCHAR
Ваши вымышленные данные фильма будут выглядеть следующим образом:
Movie Table
====================================================================================
MovieID MovieName
====================================================================================
1 Green Lantern
2 The Tick
MovieFact Table
====================================================================================
MovieID FactType FactValue FactSource FactJournalist
====================================================================================
1 Director Kubrick CHUD Sarah
1 Leading Male Robert Redford CHUD James
1 Leading Female Miley Cyrus Dark Horizons James
1 Villain Hugh Grant CHUD Sarah
2 Director Mel Gibson Yahoo Cameron
2 Leading Male John Lambert Yahoo Erica
...
Интересный сценарий. Вы можете обойти EAV-гетто, думая о своих сущностях как о первоклассных объектах; давайте назовем их Фактами. И помогает то, что в этом случае вы достаточно ортогональны, потому что в каждом фильме одинаковые четыре факта. Ваша таблица EAV может быть вашей первоначальной / правильной таблицей, и тогда у вас может быть внешний процесс, который обрабатывает эту таблицу и реплицирует данные в надлежащим образом нормализованную форму (т.е. вашу первую таблицу). Таким образом, у вас есть необходимые данные с их метаданными, и у вас есть простой способ запрашивать информацию о фильмах с точностью до того, как часто выполняется процесс добычи.
Я думаю, что вам определенно нужно что-то вроде "вне базы данных", чтобы удостовериться, что данные остаются действительными, поскольку в базе данных не существует способа сохранить целостность ваших обычных таблиц и таблиц EAV. Я предполагаю, что с помощью сложной серии триггеров вы можете в значительной степени выполнить все, но одному человеку-администратору, который "понимает" вашу проблему, вероятно, гораздо легче справиться.
Поскольку у вас есть только два поля для исходных данных (Источник и Журналист), я бы порекомендовал таблицу метаданных, например:
Movie DirectorSource DirectorJournalist LeadingMaleSource LeadingMaleJournalist ...
---------------------------------------------------------------------------------------
The Tick Yahoo Cameron ... ...
Это будет держать менее важные исходные данные вне основной таблицы, но запросы не станут сложными, и ваш код будет более читабельным.
Я бы только посоветовал EAV если...
- У вас есть более 3 полей метаданных источника
- Вы должны иметь возможность легко добавлять или изменять поля фильма. (изменения типа "злодей" на "первичный злодей" делаются несколько раз в день)
Вот еще одна идея... не стесняйтесь пробивать дыры в нем:)
Table: Movie
Columns: MovieId|Movie|Director|LeadMale|LeadFemale|Villain
Table: MovieSource
Columns: MovieSourceId|MovieId|MovieRoleId|Source|Journalist
Table: MovieRole
Columns: MovieRoleId|MovieRole
Values: 1|Director, 2|LeadMale, 3|LeadFemale, 4|Villain
Я думаю, что столбцы в таблице фильмов могут быть разных типов (в вашем примере все они являются строками / типами, но они могут быть, скажем, числовой информацией или информацией о дате, которая также имеет источник).
Однако типы столбцов для исходных данных, вероятно, не будут меняться в зависимости от типов столбцов данных фильма, поэтому вы можете использовать больше системы EAV для источника без потери целостности ваших данных.
Таблица MovieRole позволяет вам явно перечислять роли, чтобы вы могли создать надежную связь между источником и заданной ячейкой таблицы фильма.
Дан
Мой ответ может показаться слишком философским для SO. Потерпите меня.
Я думаю, что столбец "Источник" - это не данные объекта, а метаданные. Это действительно данные о том, как мы узнаем некоторые другие данные. Это делает его данными о данных, и это метаданные.
Среди причин, по которым EAV вызывает проблемы, это и тот факт, что он смешивает данные и метаданные в одной строке. Бывают случаи, когда я сознательно делал это сам, в качестве промежуточного шага к результату, которого я хочу достичь. Но я никогда не пытался смешивать данные и метаданные в моих результатах.
Я знаю, почему я никогда не делал этого, но я не могу объяснить это кратко.
Я бы принял решение на основе того, что мне нужно кодировать.
Если src/journo - просто дополнительная информация, я бы пошел к дальнейшим столбцам. Но если я знаю, что собираюсь в конечном итоге создавать сложные запросы src/journo, я бы пошел в EAV, так как будет легче искать ссылки журналиста по мета-таблице, чем идти в LeadingFemaleJournalist, VillainJournalist и т. Д.
Лично я был бы склонен сбросить метаданные src/journo в другой EAV-стиль таблицы, но использовать FK для определения таблицы определения атрибутов. Наличие текстового поля "Атрибут свободной формы" - это рецепт катастрофы - всегда контролируйте свои атрибуты через ограничение. Триггеры могут быть реализованы для улучшения ссылочной целостности, если требуется.
Для меня это сводится к точке зрения. Видите ли вы, что источники и журналисты сами по себе относятся к проблемам отношений или это просто дополнительные данные, дополняющие фильм? Следующим уровнем уточнения было бы создание различных таблиц для MovieDataSource и MovieDataJournalist, которые могли бы позволить вам сопоставить FK с таблицами, определяющими действительные источники и журналисты (а затем можно было бы получить дополнительную информацию об этих источниках / журналистах). То, что вы сделали здесь, - это установление отношения "многие ко многим" между сущностью " Фильм" и сущностью " Источник" (а также " Журналист").
Так как никто больше не пытается это сделать, я отвечу на свой вопрос. Я уверен, что EAV-подобный стол - действительно единственный путь. Чтобы хранить метаданные в каждом столбце (в данном случае в отношении источника и журналиста), вы действительно рассматриваете каждый столбец как сущность в себе, что позволяет EAV.
Вы можете пойти другими путями, например, добавить второй и третий столбец для каждого исходного столбца для хранения данных, но это определенно нарушает некоторые фундаментальные правила нормализации и, вероятно, вызовет у вас боль позже.
Хм.... я не использовал это, так что я не говорю из опыта (то есть не вините меня, если это не работает), но на первый взгляд кажется, что вы могли бы хранить "общие" данные, которые Вы знаете, что всегда будет там, как и в обычной таблице, и "метаданные", которые могут измениться как XML. Тогда возникает вопрос, как правильно сделать запрос, и я думаю, что вы могли бы сделать это, как описано ЗДЕСЬ.
Другой подход к рассмотрению - это наследование таблиц классов. В этом ответе Билла Карвина есть отличный обзор вариантов EAV и много хорошего контекста.