Модель данных опроса - как избежать EAV и чрезмерной денормализации?

Что ж, imgur не работает, поэтому я опубликую картинку позже.

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

Исходящий

Для определения обзора страны требуется 4 субъекта. Некоторый опрос родителей, страна и список вопросов. Ваши вопросы имеют внутреннюю взаимосвязь, поэтому, когда одна страна «редактирует» вопрос, вы можете отслеживать как вопрос, заданный страной, так и вопрос, из которого он пришел. Еще вам понадобится объект / таблица возможных ответов. С каждым вопросом может быть связан список возможных ответов (множественный выбор или диапазоны и т. Д.). Эти 4 должны полностью определять "АБСОЛЮТНУЮ" сторону этого.

Входящий

Сторона «INBOUND» - это всего лишь 2 новых объекта, Ответчик и ответ. Респондент прямолинеен, просто демографические данные этого человека, если вы его знаете, и здесь вы можете включить отношения обратно к стране. Каждый респондент ответил на опрос в данной стране. (Лицо может быть 1: n с Ответчиком, если человек путешествует или имеет двойное гражданство)

Ответ прост; либо это один из вариантов, перечисленных в списке возможных ответов, либо он предоставляется. Не зацикливайтесь на том, что ответом может быть число, дата и т. Д. Либо это FK, либо строка символов.

Отчетность

Отчет - это объединение всего этого ... Вы выберете страну и опрос, получите список вопросов и ответов.

Сложность ответа

Зависит от того, где вы хотите проводить расчеты. Если вы использовали столбец Varchar2 (4000) для ответов, предоставленных пользователем, вы можете добавить атрибут к вопросу, чтобы описать тип данных ответа. Q: Возраст? DT: целое число от (0 до 130). Тогда ваш уровень интеграции сможет выполнять проверку, а не базу данных. Или у вас может быть 4 столбца, один для числа, даты, символа и CLOB. И ваш уровень интеграции определит, какой столбец использовать. Когда вы сообщаете эти ответы, вы просто выбираете все четыре столбца с помощью Coalesce ().

Это EAV из-за некоторой двусмысленности в типе данных "Ответ"

Нет, это не так.

Модель EAV разбивает Сущность на список атрибутов. вот так:

Entity     Attribute     Value
  1          Fname         Stephanie
  1          Lname         Page
  1          Age           30

поскольку вы видите, что столбец «Ответ» схемы опроса содержит как слова, так и числа, как столбец «Значение», вы думаете, что это определяет EAV. Это не. Точно так же, как если бы я добавил к этой модели 3 столбца типов данных, это не изменило бы ее ИЗ EAV.

Я так ненавижу, когда

Люди говорили мне, что настраиваемый мной запрос должен выполняться «как можно быстрее». Хорошо, дай мне миллиард долларов и 30 лет. "Подождите, миллиард что?" «Пока», «настолько быстро, насколько» не являются требованиями. Вы можете проверить все, что захотите, в базе данных ... Создайте шедуар из триггеров Before, вуаля! Множество проверок.

Какой тип данных у столбца возраста? Или столбец "Дата рождения"? Зависит от вашего источника данных. Некоторые старые записи могут содержать только месяц и год, или только год, или «около» или «около» какого-то года. Вы не могли бы иметь только числовой столбец и проводить «как можно больше проверок». и НОМЕР (2) может быть ЛУЧШЕЙ проверкой, чем просто НОМЕР. Итак, теперь у вас будет НОМЕР (1), НОМЕР (2), НОМЕР ... чтобы иметь "столько, сколько".

Где, я думаю, вы запутались

Думайте об этом как о концептуальной модели данных, а не о физической. В этом смысле Survey - это сущность. Является ли Вопрос сущностью или просто атрибутом опроса. Если вы построили одну таблицу PER, вы ясно говорите, что вопрос - это просто атрибут опроса, и их вертикальное хранение делает это EAV. Эта модель показывает, что Вопрос на самом деле является другой сущностью. Между вопросами существует связь, например "страна [может] редактировать вопросы". Был исходный вопрос и отредактированный. На каждый вопрос есть набор возможных ответов. И самое главное, это все вопросы. В EAV я называю fname, lname, bdate, age, major, salary и т. Д. - все это очень разные вещи, просто атрибуты. В этом случае мы не включаем название агентства, инициировавшего опрос, дату его проведения, дату, когда нужно вернуть, и т. Д. В качестве вопросов.

Позвольте мне сказать это по-другому. Вы FedEx. Вы хотите хранить отметки времени для определенных событий. Каждый раз, когда посылка входит или покидает объект или транспортное средство. Время на грузовике-пикапе, время выхода из грузовика на первый объект, время выхода из этого пункта на самолет и т. Д. Вы храните их горизонтально? Как узнать количество хмелей заранее? Если вы храните их вертикально, автоматически ли это превращается в EAV? И если да, то почему.

Вы - метеорологическая компания, получающая данные о температуре со станций по всей стране. Допустим, датчики предназначены для отправки показаний при изменении температуры на +/- полный градус. Если вы сохраняете sensor_ID | timestamp | temp - это таблица чтения, это EAV? Каждое показание не является атрибутом датчика, они сами являются объектами, принадлежащими к коллекции / серии.

Вертикальное хранение ответов имеет одну общую черту с EAV - сложность выполнения аналитических запросов. Если вам нужен список всех людей, ответивших ВЕРНО на вопросы 5 и 10, но ЛОЖНО на вопросы 6 и 11, будет очень сложно сделать это вертикально. Может быть, поэтому вы видите это EAV. Если вы хотите это сделать, вам понадобится другое хранилище. Реляционное хранилище вопросов и ответов - не лучшая база данных для отчетов. Вернемся к примеру с FedEx. Отчеты о времени в пути, когда строки расположены вертикально, непросто.

Похоже, вы боретесь с общей проблемой: как использовать молоток для завинчивания винта.

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

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

create table responses (
  id uuid primary key,
  questionnaire_id uuid references questionnaires.id,
  data text
)

Если бы я использовал SQL Server, Express будет в порядке, тогда я бы сделал следующее:

• Таблица со списком вопросов, флагами для типа (бит), если требуется, флаг (бит), правильный ответ, если существует и т. Д.
• Таблица со списком стран
• Связывание таблиц стран и вопросов (некоторые страны могут не получить некоторые вопросы
• Таблица для ответов со столбцами для вопроса (ов) и столбец xml для дополнительных вопросов, включая те, которые добавлены

Если вы не разбираетесь в измельчении XML, используйте разреженные столбцы для всех дополнительных вопросов. Я не помню точно ограничение на количество разреженных столбцов в таблице, но я считаю, что оно превышает 30 000. SQL Server внутренне хранит разреженные столбцы как XML и будет уничтожать его, когда вы выбираете столбец, и да, он может быть проиндексирован.

На диаграмме ниже показана диаграмма, созданная с помощью SQL Server. столбец AL_A4 будет содержать ответ на QL_Id = 4 и относится к разреженному типу. QL_Id в таблице QuestionList не помечен как обязательный, что позволяет вам сделать столбец в AnswerList разреженным.

Поскольку страны будут добавлять вопросы, создайте таблицы QuestionListCustom, QuestiontoCountryCustom и AnswerListCustom и добавьте информацию из настраиваемых вопросов.

Я уверен, что есть другие способы спроектировать хранилище, так я бы сдал домашнее задание, если это не домашнее задание, то вы наверняка работаете на ООН.

Вы не думали изобретать велосипед? Уже созданы приложения для опросов с открытым исходным кодом. Даже если они не соответствуют вашим потребностям, загрузите несколько и ознакомьтесь с их моделями данных.