Как хранить метаданные в столбцах

Скажем, вы собираете инсайдерскую информацию о предстоящих выпусках супергероев, и ваш главный фильм выглядит примерно так:

Таблица 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 в сочетании с другой, которая only содержит метаданные:

Таблица 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? У вас могут быть столбцы "Director", "Director_Source", "Leading Male", "Leading Male_Source" и т.д., Но это становится очень уродливым. Есть ли лучшее решение, о котором я не думаю?

Если я не уточнил ни одного вопроса, прокомментируйте, и я добавлю по мере необходимости. О да, и данные фильма, которые я использовал, сфабрикованы:)

Изменить: Чтобы кратко изложить мой основной вопрос, я хотел бы иметь простоту и истинную конструкцию RDBMS таблицы 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

Ответ 1

Вы можете изменить то, что считаете значением факта в своем дизайне... кажется, что факт в вашей модели данных может быть выражен как следующий N-кортеж:

Movie | FactType | FactValue | FactSource | FactJournalist

Следующие структуры таблиц должны поддерживать модель данных, которую вы хотите, и ее можно легко проиндексировать и объединить. Вы также можете создать представление, которое позволяет вывести только значение факта и тип факта, чтобы вы могли создать следующую перспективу:

MovieID | Movie Name | Director | LeadingMale | LeadingFemale | PrimaryVillain | etc

Интересно, что вы могли бы считать это логическим продолжением полного применения модели EAV к данным и разложения отдельного фильма (с его интуитивной атрибуцией режиссера, ведущего, злодея и т.д.) в поворотную структуру, где атрибуты сосредоточены на источнике информации.

Преимущества предлагаемой модели данных:

он хорошо нормализуется (хотя вы должны, вероятно, нормализовать поле FactType в справочной таблице для полноты)
можно создать представление, которое эффективно сводит фактические типы в табличную структуру.
он является относительно расширяемым и позволяет базе данных обеспечивать ссылочную целостность и (при желании) ограничения мощности.
таблица MovieFact может быть подклассифицирована для поддержки различных видов фактов фильма, а не только для простых текстовых полей.
простые запросы к данным относительно эффективны

Некоторые из недостатков модели данных:

Композитные, условные запросы сложнее (но не невозможно) писать (например, найти все фильмы, в которых Director является A, а ведущий мужчина - B и т.д.).
Модель несколько менее очевидна, чем более традиционный подход, или один с участием структур EAV.
вставки и обновления немного сложнее, потому что обновление нескольких фактов требует обновления нескольких строк, а не нескольких столбцов.

У меня есть данные Movie до уровня, чтобы нормализовать структуру, и вы могли бы подтолкнуть имя фильма в структуру MovieFact для согласованности (поскольку для некоторых фильмов я могу себе представить, что даже тогда имя - это то, что вы можете захотеть отслеживать источник информация для).

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
...

Ответ 2

Интересный сценарий. Вы можете обойти гетто EAV, думая о своих сущностях как о объектах первого класса; позвольте назвать их Факты. И это помогает, что вы довольно ортогональны в этом случае, поскольку каждый фильм имеет те же четыре факта. Ваша таблица EAV может быть вашей нетронутой/правильной таблицей, а затем вы можете иметь внешний процесс, который минизирует эту таблицу и реплицирует данные в правильно нормированную форму (т.е. Вашу первую таблицу). Таким образом, у вас есть данные, которые вы хотите, с его метаданными, и у вас есть простой способ запросить информацию о фильме, точно так же, как часто выполняется ваш процесс разработки.

Я думаю, что вам определенно нужна "мышца вне базы данных", чтобы убедиться, что данные остаются в силе, поскольку, похоже, не существует какого-либо встроенного метода поддержки целостности в ваших обычных и 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

Я думаю, что столбцы в таблице фильмов могут быть разных типов (в вашем примере это все строки /varchars, но они могут быть, скажем, численной или датой, которая также имеет источник).

Типы столбцов для исходных данных, однако, вероятно, не будут меняться в зависимости от типов столбцов данных фильма, поэтому вы могли бы использовать больше системы EAV для источника, не теряя целостности своих данных.

Таблица MovieRole позволяет вам явно перечислять роли, чтобы вы могли создать верную связь между источником и данной ячейкой таблицы фильмов.

Дан

Ответ 4

Увидев, что у вас есть только два поля для исходных данных (источник и журналист), я бы рекомендовал таблицу метаданных следующим образом:

Movie    DirectorSource  DirectorJournalist  LeadingMaleSource  LeadingMaleJournalist ...
---------------------------------------------------------------------------------------
The Tick   Yahoo           Cameron           ...                ...

Это позволит сохранить менее важные исходные данные из основной таблицы, но запросы не будут усложняться, и ваш код будет более читаемым.

Я бы посоветовал только EAV, если...

У вас есть более трех полей метаданных источника
Вы должны иметь возможность добавлять или изменять поля фильма легко. (такие изменения, как "Злодей" в "Первичный злодей", выполняются несколько раз в день).

Ответ 5

Мой ответ может показаться слишком философским для SO. Медведь со мной.

Я думаю, что столбец "Источник" - это не предметные данные, а метаданные. Это действительно данные о том, как мы узнаем некоторые другие данные. Это делает данные о данных и метаданных.

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

Я знаю, почему я этого не делал, но я не могу объяснить это кратко.

Ответ 6

Поскольку никто другой не делает трещины на этом, я собираюсь ответить на свой вопрос. Я уверен, что EAV-подобный стол действительно единственный способ. Чтобы хранить метаданные в каждом столбце (в данном случае источник и журналист), вы действительно рассматриваете каждый столбец как объект в себе, что позволяет EAV.

Вы могли пойти на другие маршруты, например добавить второй и третий столбцы для каждого исходного столбца для хранения данных, но это определенно нарушает некоторые основные правила нормализации и, вероятно, только причинит вам боль позже.

Ответ 7

Хм.... Я не использовал это, поэтому я не говорю по опыту (то есть не обвиняйте меня, если он не работает), но на первый взгляд кажется, что вы можете хранить "общие" данные, которые, как вы знаете, всегда будут там, как в обычной таблице, и "метаданные", которые могут быть изменены как XML. Вопрос тогда в том, как запросить его красиво, и я думаю, что вы могли бы сделать это, как описано ЗДЕСЬ.

Ответ 8

Еще один подход к рассмотрению - наследование класса. Билл Карвин отлично разбирается в вариантах EAV в этом SO-ответе и в хорошем контексте.

Ответ 9

Я бы принял решение на основе того, что мне нужно для кода.

Если src/journo - это просто дополнительная информация, я бы пошел на дальнейшие столбцы. Но если я знаю, что собираюсь завершить сложные запросы src/journo, я бы пошел EAV, так как будет легче искать журналиста, ссылаясь на мета-таблицу, чем на то, чтобы попасть в LeadingFemaleJournalist и VillainJournalist и т.д.

Лично - я был бы склонен давать метаданные src/journalo в другой таблице EAV-стиля, но использовать FK для определения таблицы определения атрибутов. Наличие текстового поля атрибута произвольной формы - это рецепт катастрофы - всегда контролируйте свои атрибуты с помощью ограничения. Триггеры могут быть реализованы для улучшения ссылочной целостности, если это необходимо.

Для меня это сводится к точке зрения. Вы видите, что источники и журналисты относятся к реляционным проблемам сами по себе или представляют собой дополнительные данные, дополняющие фильм? Следующим уровнем уточнения будет создание разных таблиц для MovieDataSource и MovieDataJournalist, которые могут позволить вам сопоставлять FK с таблицами, определяющими действительные источники и журналисты (и дальнейшая информация об этих Источниках/Журналистах затем может быть описана). То, что вы сделаете здесь, - установить связь "много-ко-многим" между субъектом Movie и объектом Source (а также журналистом).