Ведение истории изменений данных - лучшая практика?

Question

Рассмотрим базу данных с несколькими (3-4) таблицами с большим количеством столбцов (от 15 до 40). В каждой таблице есть несколько тысяч записей, сгенерированных в год, и около десятка изменений, внесенных для каждой записи.

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

По некоторым причинам мы не можем хранить "окончательные" и "исторические" данные в одной и той же таблице (поэтому мы не можем добавить некоторые столбцы в наши таблицы, чтобы сохранить некоторую информацию о версиях, например, как это делает Wordpress, когда дело доходит до сохранения истории редактирования сообщений),

Как лучше всего подойти к этой проблеме? Я думал о двух решениях:

---

1. Для каждой отслеживаемой таблицы у нас есть зеркальная таблица с такими же столбцами и дополнительными столбцами, в которых мы храним информацию о версиях (например, временные метки, идентификатор "исходной" строки и т. Д.)

Плюсы:

• мы храним данные точно так же, как это было в исходных таблицах

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

Минусы:

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

---

2. Мы создаем одну таблицу для "исторических" ревизий. Мы храним некоторую ревизионную информацию, такую ​​как отметки времени и т. Д., А также отслеживаем, из какой таблицы происходят данные. Но исходная строка данных хранится в большом текстовом столбце в формате JSON.

Плюсы:

• у нас есть только одна таблица истории для всех отслеживаемых таблиц

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

Минусы:

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

---

3. Может быть, какое-то другое решение?

---

Как лучше всего хранить историю версий в такой системе?

Дополнительная информация:

• каждая из отслеживаемых таблиц может измениться в будущем (т.е. будут добавлены новые столбцы),

• количество отслеживаемых таблиц может измениться в будущем (т. е. будут добавлены новые таблицы).

К вашему сведению: мы используем laravel 5.3 и базу данных mysql.

Answer 1

Как часто вам нужен доступ к данным аудита? Стоит ли беспокоиться о стоимости хранения? Нужно ли вам это в той же системе, что и обычные данные?

По сути, наличие таблицы с именем foo и второй таблицы с именем foo_log не является редкостью. Это также позволяет вам хранить foo_log где-то по-другому, даже, возможно, вторичную БД. Если foo_log находится на диске шпинделя, а foo на флэш-памяти, вы все равно получаете быстрое чтение, но вы получаете несколько более дешевое хранилище резервных копий.

Если вам никогда не нужно отображать эти данные, а нужно просто по юридическим причинам или чтобы выяснить, как что-то пошло не так, единственная таблица - не страшный план.

Но если проблема заключается в резервном копировании, как это может показаться, почему бы просто не делать резервные копии базы данных MySQL на регулярной основе и хранить резервные копии в другом месте?