Храните все изменения данных с каждой информацией (например, Stackoverflow)

У меня есть система, написанная с использованием Codeigniter и как база данных с использованием MySQL. У системы есть пользователь, пользовательские группы с разными привилегиями и т. Д. Имеют много таблиц mysql, которые имеют много разных отношений.

Некоторые из таблиц, которые у меня есть:

  • Предметы
  • контракты
  • клиенты
  • продукты
  • product_features
  • заказы
  • order_features
  • order_products
  • и т.д…

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

A user changed product features with id of A8767 B user added new customer with id 56 C user edited content of orderlist A user added new product (id: A8767) to order (id: or67) ... 

Я хочу сохранить все изменения, которые были сделаны с каждой деталью, например, редактировать историю вопроса Stackoverflow. Я могу подумать о дизайне log_table чтобы сохранить все изменения данных из разных таблиц. Есть ли способ, учебник, движок, плагин для этого? Только я могу думать о дублировании каждой таблицы и сохранять изменения на них, но я не считаю ее хорошим способом.

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

Кстати, существует реализация для «версий» табличных данных в доктрине ORM mapper. См. Этот пример в своих документах . Возможно, это соответствует вашим потребностям, но это не подходит мне. Кажется, что все исторические данные удаляются, когда исходная запись удаляется, что делает ее действительно не безопасной.

Вариант A: иметь копию каждой таблицы для хранения данных ревизии

Допустим, у вас есть простая таблица контактов:

 CREATE TABLE contact ( id INT NOT NULL auto_increment, name VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255), PRIMARY KEY (id) ) 

Вы должны создать копию этой таблицы и добавить данные ревизии:

 CREATE TABLE contact_revisions ( id INT NOT NULL, name VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255), revision_id INT auto_increment, type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL, change_time DEFAULT current_timestamp, PRIMARY KEY(revision_id) ) 

Следите за INSERT и UPDATE с помощью триггеров AFTER . При каждой новой ревизии данных в оригинале вставьте копию новых данных в таблицу ревизий и правильно настройте type изменения.

Чтобы зарегистрировать безопасный DELETE вы также должны вставить новую строку в таблицу истории! Для этого вы должны использовать триггер BEFORE DELETE и сохранять последние значения до их удаления. В противном случае вам придется удалить каждое ограничение NOT NULL в таблице истории.

Некоторые важные замечания относительно этой реализации

  • Для таблицы истории вы должны удалить каждый UNIQUE KEY (здесь: PRIMARY KEY ) из таблицы ревизий, потому что у вас будет один и тот же ключ несколько раз для каждой ревизии данных.
  • Когда вы ALTER схему и данные в исходной таблице посредством обновления (например, обновление программного обеспечения), вы должны обеспечить, чтобы те же данные или схемы были применены к таблице истории и ее данным. В противном случае вы столкнетесь с проблемами при возврате к более старой версии набора записей.
  • В реальной реальности вы хотели бы знать, какой пользователь изменил данные. Чтобы эта ревизивно безопасная запись пользователя никогда не удалялась из таблицы пользователей. Вы должны просто отключить учетную запись с флагом.
  • Обычно одно действие пользователя включает несколько таблиц. В реализации реального мира вам также необходимо будет отслеживать, какие изменения в нескольких таблицах относятся к одной пользовательской транзакции, а также в каком порядке. В реальном случае вы хотели бы вернуть все изменения одной транзакции вместе, в обратном порядке. Для этого потребуется дополнительная таблица ревизий, которая отслеживает пользователей и транзакции и сохраняет свободную связь со всеми этими отдельными ревизиями в таблицах истории.

Выгоды:

  • полностью в базе данных, независимо от кода приложения. (ну, а не при отслеживании пользовательских транзакций важно, что потребует некоторой логики вне сферы действия одного запроса)
  • все данные находятся в исходном формате, без имплицированных преобразований типов.
  • хорошая производительность при поиске в версиях
  • легкий откат. Просто выполните простую INSERT .. ON DUPLICATE KEY UPDATE .. в исходной таблице, используя данные из ревизии, которую вы хотите отменить.

Существо дела:

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

Как уже говорилось выше, доктрины с versionable делают что-то похожее.


Вариант B: иметь таблицу журналов центрального изменения

предисловие: плохая практика, показанная только для иллюстрации альтернативы.

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

У вас есть таблица центральной истории, которая отслеживает

  • Кто
  • когда
  • изменять, вставлять или удалять
  • какие данные
  • в какой области
  • из которых таблица

Как и в другом подходе, вы также можете отслеживать, какие изменения отдельных данных относятся к одному действию / транзакции пользователя и в каком порядке.

Выгоды:

  • не нужно синхронизировать с исходной таблицей при добавлении полей в таблицу или создании новой таблицы. он масштабируется прозрачно.

Существо дела:

  • неправильная практика с использованием простого значения = хранилище ключей в базе данных
  • плохая производительность поиска, из-за неявного преобразования типов
  • может замедлить общую производительность приложения / базы данных, когда центральная таблица истории становится узким местом из-за блокировок записи (это применимо только для определенных движков с блокировками таблиц, то есть MyISAM)
  • Сделать откаты намного сложнее
  • возможные ошибки преобразования данных / прецизионные потери из-за неявного преобразования типов
  • не отслеживает изменения при прямом доступе к базе данных где-то в вашем коде вместо использования вашего уровня модели / данных и забывает, что в этом случае вы должны вручную записывать журнал изменений. Может быть большой проблемой при работе в команде с другими программистами.

Вывод:

  • Вариант B может быть очень удобен для небольших приложений как простое «падение», когда его просто для ведения журнала изменений.
  • Если вы хотите вернуться во времени и сможете легко сравнить различия между историческим revison 123 до версии 125 и / или вернуться к старым данным, тогда вариант A – это трудный путь.

Как насчет использования общей таблицы обновления uni. Поля таблицы должны содержать следующие значения:

user,event,date,table,field,new value

  • пользователь – внесший изменения
  • event – как код предопределенных событий (обновление, сохранение, вставка)
  • дата – когда изменение было сделано
  • table & field – может быть автоматически локализовано из глобального запроса
  • значение – вставленное значение

Значения и вставка могут быть созданы с помощью некоторой функции из общего запроса.