Как спроектировать базу данных SQL с отменой повтора?

Я пытаюсь понять, как спроектировать свои таблицы БД, чтобы разрешить отмену-повтор.

Представьте, что у вас есть таблица tasks со следующей структурой:

id <int>
title <varchar>
memo <string>
date_added <datetime>
date_due <datetime>

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

  1. Будете ли вы иметь отдельную таблицу для отслеживания изменений или попытаетесь сохранить изменения в таблице tasks ("призрачные" строки, из-за отсутствия лучшего термина)?
  2. Будете ли вы отслеживать все столбцы или только те, которые каждый раз менялись?

Если это имеет значение, я использую MySQL. Кроме того, если это имеет значение, я хотел бы иметь возможность показывать историю (аля Photoshop) и позволять пользователю переключаться на любую версию.

Дополнительный вопрос: вы бы сохранили всю ячейку memo при изменении или попытались бы сохранить только дельту? Причина, по которой я спрашиваю, заключается в том, что ячейка memo может быть большой, и в каждой версии может быть изменено только одно слово или символ. Конечно, сохранение дельты потребует синтаксического анализа, но если отмены не ожидаются очень часто, не лучше ли сэкономить место, а не время обработки?

Спасибо за помощь.


mysql sql delta undo-redo audit-tables
person brian h    schedule 26.03.2011    source источник
comment
Аналогичный вопрос здесь. Взгляните на мой ответ   -  person Ronnis    schedule 27.03.2011

Ответы (3)


arrow_upward
7
arrow_downward

Я бы создал таблицу истории для вашей таблицы задач. Та же структура, что и у задач + новое поле с именем previousId. Это будет содержать предыдущий идентификатор изменения, поэтому вы можете вернуться к различным изменениям (отменить / повторить).

Зачем нужна новая таблица History? По простой причине: не перегружайте таблицу задач тем, для чего она не предназначена.

Что касается места, то в Истории вместо Памятки используйте бинарный формат и заархивируйте содержимое текста, которое хотите сохранить. Не пытайтесь обнаружить изменения. Вы столкнетесь с ошибочным кодом, что приведет к разочарованию и потере времени...

Оптимизация: Еще лучше, вы можете оставить только три столбца в таблице History: 1. taskId (внешний ключ к задачам) 2. data - бинарное поле. Перед сохранением в таблице «История» создайте строку XML, содержащую только измененные поля. 3. previousId (поможет поддерживать очередь изменений и позволит перемещаться вперед и назад)

Что касается поля данных, создайте строку XML следующим образом:

<task>
  <title>Title was changed</title>
  <date_added>2011-03-26 01:29:22<date_added>
</task>

Это в основном скажет вам, что на этот раз вы изменили только поля title и date_added.

После создания строки XML просто заархивируйте ее, если хотите, и сохраните в поле данных таблицы истории.

XML также обеспечивает гибкость. Если вы добавляете/удаляете поле в таблице задач, вам также не нужно обновлять таблицу «История». Таким образом, структура таблицы задач и таблицы истории разделены, поэтому вам не нужно каждый раз обновлять две таблицы.

PS: не забудьте добавить некоторые индексы для быстрой навигации по таблице истории. Поля для индексации: taskId и previousId, так как вам понадобятся быстрые запросы к этой таблице.

Надеюсь это поможет.

person Adi    schedule 26.03.2011
comment
Кстати, сжатие уменьшит размер вашего текста до 5% от исходного текста. Обычное значение составляет около 10%, но если у вас есть общие повторяющиеся слова, вы получите еще лучшее сжатие. - person Adi; 31.03.2011
comment
это умно, но я не уверен, что понимаю идею трех полей. Конечно, каждая запись в таблице истории должна иметь свое собственное поле идентификатора (автоинкремент), внешнюю ссылку на идентификатор записи задачи и ссылку на идентификатор предыдущей записи истории, если это применимо (т. е. иметь тот же идентификатор записи задачи)... или есть что-то, чего я не понял? - person mike rodent; 18.08.2014
comment
@mikerodent Я думаю, вы поняли идею, нам нужно 2 ключа: один для таблицы задач (taskId) и один для таблицы истории (previousId) плюс полезная нагрузка (данные), которая изменилась, чтобы вы могли перемещаться по истории на основе поля taskId и previousId, а также получить доступ к измененным данным и восстановить их при необходимости. - person Adi; 04.09.2014

arrow_upward
3
arrow_downward

Когда я делаю подобные вещи с помощью SQL, я всегда использую вторую таблицу для истории изменений. Это предотвратит чрезмерное увеличение вашей основной таблицы с помощью версий. Причина в том, что извлечение текущей записи происходит почти в 100% случаев, просмотр истории и откат назад (отмена) происходят очень редко.

Если у вас есть только одна отмена или история, то отслеживание в таблице, вероятно, подойдет.

Хотите ли вы сохранить дельты или всю ячейку, зависит от ожидаемого роста/использования. Если вам удобно создавать логику для управления дельтами, это сэкономит вам место. Если вещи действительно не создают новые версии, которые часто я бы не начинал с этого (применяя YAGNI)

person Steve    schedule 26.03.2011

arrow_upward
3
arrow_downward

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

Тем не менее, более старые и более новые дельты требуют большой обработки, если у вас нет какой-либо не-дельты, на которой можно основываться. Дельты от более новых к более старым требуют повторной обработки каждый раз, когда что-то меняется. Таким образом, дельты обычно не дают вам много преимуществ, но большую сложность.

Последнее, что я проверял несколько лет назад, MediaWiki, программное обеспечение, стоящее за Википедией, хранило полные тексты и предоставил некоторые средства для сжатия старых версий с помощью gzip для экономии места и выделенную таблицу archive для удаленных версий / страниц.

На их веб-сайте есть диаграмма ER структуры их базы данных, которая может оказаться полезной.

person Arc    schedule 26.03.2011