SQL дизайн для опроса с ответами разных типов данных
Я работаю над онлайн-опросом. Большинство вопросов имеют шкалу от 1 до 5 для ответа. Если нам нужно добавить вопрос в опрос, я использую простую веб-форму, которая делает ВСТАВКУ в соответствующую таблицу, и вуаля! опросы задают новый вопрос - нет нового кода или изменений в структуре базы данных.
Нас просят добавить вопросы опроса, которые могут иметь ответы разных типов данных. Задача состоит в том, чтобы опрос был "настраиваемым", чтобы в любой момент в будущем, когда кто-то говорит: "Нам нужен новый опрос, который задает {текстовый ответ на вопрос}, {1-5 вопрос}, {истинный ложный вопрос}, {вопрос с датой в качестве ответа}", мы можем сделать это без изменения структуры базы данных.
Я пытаюсь придумать лучший способ сохранить эти ответы, но каждый метод, который я придумаю, кажется несколько хакерским.
У некоторых вопросов может быть ответ да / нет или правда / ложь, у некоторых может быть целочисленный ответ ( "Сколько раз за последний месяц вы пользовались технической поддержкой?"), У другого ответа может быть дата, строка, кратное число выбор с одним значением, множественный выбор с несколькими значениями и т. д. Или иногда конкретное значение ответа может вызвать подвопрос ( "Что вас разочаровало...?")
Простое решение состоит в том, чтобы каждый вопрос был представлен в виде столбца в опросе, а ответ - в виде столбца в опросе, и нужно ли задавать его в качестве столбца в опросе. Мне это кажется беспорядком - это один большой стол; не очень "реляционный".
Мозговой штурм, "лучший" метод, который я мог бы придумать, - это иметь разные таблицы для каждого типа ответа, но он чувствителен к проблемам целостности данных. Другими словами, я бы
CREATE TABLE `Questions` (...);
CREATE TABLE `TrueFalseAnswers` (...);
ALTER TABLE `TrueFalseAnswers`
ADD CONSTRAINT `TrueFalseAnswers_ibfk_1` FOREIGN KEY (`question_id`)
REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE `TextAnswers` (...);
ALTER TABLE `TextAnswers`
ADD CONSTRAINT `TextAnswers_ibfk_1` FOREIGN KEY (`question_id`)
REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
и т.п.
Одна из проблем, связанных с вышеизложенным, заключается в том, что я не могу гарантировать, что по крайней мере один и только один ответ существует на любой вопрос только в DDL.
Другим решением может быть наличие двоичного или строкового столбца для ответа в таблице "Вопросы", кодирование всех ответов в некоторый строковый или двоичный формат и сохранение их в столбце ответов. Это дает мне по крайней мере один и только один ответ на каждый вопрос, но затем я теряю доступ к агрегатным функциям в запросах SQL. Это кажется мне не очень "реляционным" решением.
Итак, я вижу проблемы с идеями, изложенными выше. Есть ли "лучший" способ решить эту проблему?
Теперь, когда я потратил время на вербализацию проблемы и моих мыслей, кажется, что общая проблема, которую я представляю, - "Я хотел бы хранить данные произвольного типа без какого-либо кодирования..." безнадежна?
Я использую MySQL, поэтому у меня нет доступа к вещам, которые могут иметь другие СУБД.
4 ответа
Используйте столбец, в котором указан тип ответа, но сохраните ответ в виде текста. Ваше приложение или интерфейс могут использовать столбец answer_type, чтобы определить, что отображать конечному пользователю (тестовое поле, переключатели, средство выбора даты) и как проверить его перед отправкой обратно в базу данных.
Вы хотите создать таблицу QuestionType, которая соответствует классу QuestionType. Позвольте постоянному ответу, заполненному вашими пользователями, быть текстом произвольной формы, и оставьте его на усмотрение типа вопроса, чтобы определить, что означает ответ.
Итак, если это правда / ложь, ответ может быть "Т" или "F".
Если это множественный выбор, Ответ может быть индексом выбранного выбора.
Если это текстовое поле, которое заполняет пользователь, сохраните введенный текст.
я представляю: "Я хотел бы хранить данные произвольного типа без какого-либо кодирования..." Это безнадежно?
Да, это в значительной степени так. Не существует "хорошего" решения проблемы, которую вы ставите. "Лучшее" - как описали его Дейв Сверски и Ларри Люстиг:
Таблица Вопросов, в которой хранится вопрос, возможные ответы (если это множественный выбор) и тип вопроса
Таблица ответов, в которой хранится ответ на вопрос (таблица от FK до вопроса), сериализованная в виде текста. Тип данных Varchar(4000) или TEXT, предпочтительно первый, за исключением случаев, когда это абсолютно необходимо.
Решение о том, что означает значение, зависит от логики вашего приложения на основе типа, указанного для вопроса.
Вы также можете использовать указанные таблицы dataType, так что таблица для целых чисел, дат, строк и т. Д. Оттуда 1 таблица ответов, которая связывает вопрос (таблицу) с правильной таблицей dataType + первичный ключ.
Чтобы иметь общий обзор всех вопросов с ответами, вы можете создать представление поверх этого, просто приведя все типы данных к тексту в представлении.