Дизайн базы данных для опроса
Мне нужно создать опрос, где ответы хранятся в базе данных. Мне просто интересно, что было бы лучшим способом реализовать это в базе данных, в частности, необходимые таблицы. Опрос содержит различные типы вопросов. Например: текстовые поля для комментариев, вопросы с несколькими вариантами ответов и, возможно, вопросы, которые могут содержать более одного ответа (т.е. отметьте все подходящие варианты).
Я придумал два возможных решения:
Создайте гигантскую таблицу, которая содержит ответы на каждую заявку на опрос. Каждый столбец будет соответствовать ответу из опроса. т.е. SurveyID, Ответ1, Ответ2, Ответ3
Я не думаю, что это лучший способ, так как в этом опросе много вопросов, и он не кажется слишком гибким, если опрос будет изменен.
Другая вещь, о которой я подумал, это создание таблицы вопросов и таблицы ответов. Таблица вопросов будет содержать все вопросы для опроса. Таблица ответов будет содержать индивидуальные ответы из опроса, каждая строка связана с вопросом.
Простой пример:
tblSurvey: SurveyID
tblQuestion: QuestionID, SurveyID, QuestionType, Question
tblAnswer: AnswerID, UserID, QuestionID, Ответ
tblUser:идентификатор пользователя, имя пользователя
Моя проблема в том, что может быть множество ответов, что сделает таблицу ответов довольно большой. Я не уверен, что это так здорово, когда дело доходит до производительности.
Буду признателен за любые идеи и предложения.
11 ответов
Я думаю, что ваша модель №2 хороша, однако вы можете взглянуть на более сложную модель, которая хранит вопросы и заранее подготовленные ответы (предлагаемые ответы) и позволяет их повторно использовать в различных опросах.
- В одном опросе может быть много вопросов; один вопрос может быть (повторно) использован во многих опросах.
- Один (заранее сделанный) ответ может быть предложен на многие вопросы. На один вопрос может быть предложено много ответов. Вопрос может иметь разные ответы, предлагаемые в разных опросах. Ответ может быть предложен на разные вопросы в разных опросах. По умолчанию есть ответ "Другой", если человек выбирает другой, его ответ записывается в Answer.OtherText.
- Один человек может участвовать во многих опросах, один человек может ответить на конкретный вопрос в опросе только один раз.
Мой дизайн показан ниже.
Последний сценарий создания находится по адресу https://gist.github.com/durrantm/1e618164fd4acf91e372
Сценарий и файл mysql workbench.mwb также доступны по адресу
https://github.com/durrantm/survey
Определенно, вариант № 2, также я думаю, что вы можете упустить текущую схему, вам может понадобиться другая таблица:
+-----------+
| tblSurvey |
|-----------|
| SurveyId |
+-----------+
+--------------+
| tblQuestion |
|--------------|
| QuestionID |
| SurveyID |
| QuestionType |
| Question |
+--------------+
+--------------+
| tblAnswer |
|--------------|
| AnswerID |
| QuestionID |
| Answer |
+--------------+
+------------------+
| tblUsersAnswer |
|------------------|
| UserAnswerID |
| AnswerID |
| UserID |
| Response |
+------------------+
+-----------+
| tblUser |
|-----------|
| UserID |
| UserName |
+-----------+
Каждый вопрос, вероятно, будет иметь определенное количество ответов, из которых пользователь может выбирать, затем фактические ответы будут отслеживаться в другой таблице.
Базы данных предназначены для хранения большого количества данных и наиболее хорошо масштабируются. Нет никакой реальной необходимости использовать меньшую нормальную форму, просто чтобы больше экономить место.
Как правило, изменение схемы, основанное на том, что пользователь может изменить (например, добавление вопроса в опрос), следует считать довольно вонючим. Есть случаи, когда это может быть уместно, особенно когда имеешь дело с большими объемами данных, но знай, что ты получаешь, прежде чем погрузиться. Наличие только таблицы "ответов" для каждого опроса означает, что добавление или удаление вопросов потенциально очень дорого обходится. и очень трудно делать аналитику не зависящим от вопросов способом.
Я думаю, что ваш второй подход лучше, но если вы уверены, что у вас будет много масштабных проблем, одна вещь, которая работала для меня в прошлом, это гибридный подход:
- Создайте подробные таблицы ответов для хранения ответов на вопрос, как вы описали в 2. Эти данные, как правило, не запрашиваются напрямую из вашего приложения, но используются для создания сводных данных для таблиц отчетов. Возможно, вы также захотите реализовать некоторую форму архивирования или удаления этих данных.
- Также создайте таблицу ответов от 1 при необходимости. Это можно использовать всякий раз, когда пользователи хотят видеть простую таблицу результатов.
- Для любой аналитики, которую необходимо выполнить для целей отчетности, запланируйте задания для создания дополнительных сводных данных на основе данных из 1.
Это абсолютно много работы, поэтому я бы не советовал, если бы вы не знали наверняка, что эта таблица столкнется с серьезными проблемами.
Вы можете сохранить всю форму в виде строки JSON.
Не уверен насчет вашего требования, но этот подход будет работать при некоторых обстоятельствах.
Второй подход лучше.
Если вы хотите еще больше нормализовать его, вы можете создать таблицу для типов вопросов.
Простые вещи, которые нужно сделать:
- Поместите базу данных и войдите на свой собственный диск, а не все на C по умолчанию
- Создайте базу данных настолько большой, насколько это необходимо, чтобы у вас не было пауз во время роста базы данных
У нас были таблицы журналов в SQL Server Table с десятками миллионов строк.
№ 2 выглядит хорошо.
Для таблицы только с 4 столбцами это не должно быть проблемой, даже с несколькими миллионами строк. Конечно, это может зависеть от того, какую базу данных вы используете. Если это что-то вроде SQL Server, то это не будет проблемой.
Возможно, вы захотите создать индекс в поле QuestionID, в таблице tblAnswer.
Конечно, вам необходимо указать, какую базу данных вы используете, а также приблизительные объемы.
Наличие большой таблицы ответов само по себе не является проблемой. Пока индексы и ограничения хорошо определены, у вас все должно быть в порядке. Ваша вторая схема выглядит хорошо для меня.
Номер 2 правильный. Используйте правильный дизайн до тех пор, пока не обнаружите проблему с производительностью. У большинства СУБД не будет проблем с узкой, но очень длинной таблицей.
При правильном индексе ваше второе решение нормализовано и подходит для традиционной системы реляционных баз данных.
Я не знаю, насколько огромен, но он должен без проблем выдержать пару миллионов ответов.
Выглядит довольно полно для обзора улыбок. Не забудьте добавить таблицу для "открытых ценностей", где клиент может высказать свое мнение через текстовое поле. Свяжите эту таблицу с внешним ключом к вашему ответу и поместите индексы во все ваши реляционные столбцы для повышения производительности.