Наиболее эффективный дизайн базы данных для этих данных

Я бы сказал, что мои знания баз данных разумны, я использую для этого MySQL (InnoDb), а также проделал некоторую работу с Postgres. Так или иначе...

  • У меня большое количество вопросов типа «да» или «нет».
  • В одном и том же опросе может принять участие большое количество людей.
  • Пользователь может выбрать любой вариант, и это будет записано в базе данных.
  • Позже пользователь может передумать и поменять местами варианты, которые потребуют обновления сохраненных данных.

Мой текущий план хранения этих данных:

  • POLLID, USERID, РЕШЕНИЕ, TIMESTAMP

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

Чтобы добавить их выбор, мне пришлось бы запросить, голосовали ли они раньше, и вставить, в противном случае обновить. Если я хочу увидеть результаты опроса, мне нужно будет перебирать все решения (хотя и проиндексированные части) каждый раз, когда кто-то хочет увидеть опрос.

Мои вопросы

  1. Есть ли более эффективный способ сохранить/запросить это?
  2. Будет ли у меня индекс POLLID или POLLID и USERID (может быть, просто уникальное ограничение)? Или другой?
  3. Дополнительный побочный вопрос: почему у меня нет возможности выбирать индексы HASH и BTREE для моих таблиц, как в Postgres?

mysql sql database voting
person Z-Mehn    schedule 17.09.2012    source источник
comment
Зачем вам повторять все решения, чтобы увидеть результаты опроса? Что мешает вам иметь таблицу, в которую вы обновляете информацию об опросе каждый раз, когда подается голос? Это экономит ресурсы, необходимые для повторения всего, чтобы получить данные.   -  person N.B.    schedule 17.09.2012
comment
Будьте осторожны с предложением NB, хотя оно может сработать — это может привести к денормализации, поскольку ваши голоса могут не отражать информацию об опросе. Лучше всего вычислять эту информацию с помощью SQL, он достаточно мощный.   -  person Zeritor    schedule 17.09.2012
comment
На самом деле это не лучший способ сделать это. Отдать/обновить голосование - счетчик увеличения/уменьшения хранится где-то еще. Избавляет вас от повторения и суммирования каждый раз, когда кто-то подключается. Если реализовано правильно (и я действительно не понимаю, как это может быть реализовано плохо, поскольку это тривиально), он работает так, как и следовало ожидать.   -  person N.B.    schedule 17.09.2012
comment
Я думал об этом, но проигнорировал по причине, указанной Зеритором. Кроме того, как бы вы отслеживали, кто за что голосовал, если бы у вас не было моего дизайна в дополнение к счетчику? Это означало бы, что у id есть избыточные данные (не было бы в нормальной форме). Плюс я не мог получить даты голосования ... верно?   -  person Z-Mehn    schedule 17.09.2012
comment
Да, реализация тривиальна, но требует дополнительных действий при каждом голосовании. Что делать, если одно действие выполняется, а другое не выполняется (проблемы с подключением, проблемы с загрузкой базы данных)? Ваши данные больше не складываются правильно. SQL предназначен для того, чтобы вы использовали его для ответов на свои вопросы, вы не должны принимать значения, а затем выполнять свою собственную обработку.   -  person Zeritor    schedule 17.09.2012
comment
Итак, вопрос касается производительности, но материализованные представления вызывают неодобрение? Как только пользователь отдает или изменяет свой голос, кто говорит, что вы не можете обновить таблицу, содержащую голоса, отсортированные по пользователям, а затем обновить таблицу, содержащую статистику опроса? Это стандартная практика, используемая, когда вам нужна производительность. Но если вы проповедник SQL, то зачем гнаться за производительностью?   -  person N.B.    schedule 18.09.2012
comment
^ Спасибо за предложение "материализованного представления".   -  person Z-Mehn    schedule 20.09.2012

Ответы (1)


arrow_upward
1
arrow_downward

Дизайн звучит хорошо, несколько идей:

Таблица для опросов: id опроса, вопрос.

Таблица для выбора: id выбора, текст.

Таблица для привязки опросов к вариантам: идентификатор опроса->идентификаторы выбора.

Таблица для пользователей: данные пользователя, идентификаторы пользователей.

Таблица голосов: (идентификатор пользователя, идентификатор опроса), идентификатор выбора, отметка времени. (скобки - уникальная пара)

Вставка/обновление для одного пользователя будет работать нормально, так как вы можете просто проверить, существует ли запись для идентификатора пользователя и идентификатора опроса.

Вы можете просматривать результаты намного проще, чем повторять их, используя COUNT.

e.g.: SELECT COUNT(*) FROM votes WHERE pollid = id AND decision = choiceid

Это скажет вам, сколько людей проголосовало за "choiceid" в опросе "pollid".

Позднее редактирование:

Это способ вставки, если он не существует, и обновления, если он существует:

IF EXISTS (SELECT * FROM TableName WHERE UserId='Uid' AND PollId = 'pollid')
    UPDATE TableName SET (set values here) WHERE UserId='Uid' AND PollId = 'pollid'
ELSE   
    INSERT INTO TableName VALUES (insert values here)
person Zeritor    schedule 17.09.2012
comment
Спасибо, похоже, хороший ответ. Я сказал повторить, потому что не знал внутренней работы функции подсчета. Что-нибудь известно о последнем вопросе? - person Z-Mehn; 17.09.2012
comment
Извините, я мало что знаю об индексации и никогда не использовал postgres. - person Zeritor; 17.09.2012
comment
Можно написать операторы INSERT/UPDATE таким образом, чтобы они не обновляли никакие строки, если это невозможно, но они не сбой. Таким образом, можно проверить, скажем, на основе того, преуспели ли они в INSERT, но «обновленные строки» оператором равны 0... Вам не нужна таблица перекрестных ссылок опроса/выбора, если варианты используются только для одного опрос (не уверен, что актуально). И технически сохранение как choice_id, так и poll_id в таблице голосов является денормализацией, но правильная нормализация сделала бы запросы несколько сложными. - person Clockwork-Muse; 17.09.2012
comment
@ X-Zero, это действительно денормализация? Мне кажется нормально, но могу ошибаться. Не могли бы вы объяснить подробнее, мне было бы интересно узнать. Я не вижу другого способа сохранить все голоса. - person Zeritor; 17.09.2012
comment
Это связано с тем, что взаимосвязь между опросами и вариантами выбора уже сохранена в другом месте, поэтому это ненужная/возможно искаженная информация (неправильный выбор для опроса). Правильным было бы сохранить (user_id, choice_id), а затем просмотреть отношение, чтобы убедиться, что пользователь не проголосовал за конкретный опрос. Тем не менее, выборочная денормализация таким образом вероятно допустима, особенно если выбор предоставляется как переменная хоста, а опрос выполняется из таблиц отношений (это означает, что UPDATE занимают немного больше времени, но запросы намного проще). ). - person Clockwork-Muse; 17.09.2012
comment
Ах да, теперь я вижу. Поскольку уже существует таблица, связывающая, какой опрос имеет какие ответы, тогда да, сохранение идентификатора опроса в таблице голосов денормализует. Я этого не видел. - person Zeritor; 17.09.2012
comment
Отличная информация, это то, на что я надеялся. X-zero, как мне сделать эти безотказные вставки/обновления? Вы имеете в виду настройку уникальных ограничений, чтобы вставка отклонялась, если она уже существует? - person Z-Mehn; 18.09.2012
comment
@ Z-Mehn, я обновил свой основной пост возможной идеей. Хотя на самом деле я его не тестировал. - person Zeritor; 19.09.2012
comment
Взгляните на ‹dev.mysql.com/doc /refman/5.7/en/insert-on-duplicate.html › для лучшего способа вставки, если он не существует, и обновления, если он существуетINSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; - person Don; 14.07.2016