Дизайн базы данных с историей изменений

Ответ 1

Один из способов состоит в том, чтобы иметь MyTableNameHistory для каждой таблицы в вашей базе данных и сделать ее схему идентичной схеме таблицы MyTableName, за исключением того, что в главном ключе таблицы истории есть еще один столбец с именем effectiveUtc как DateTime. Например, если у вас есть таблица с именем Employee,

Create Table Employee
{
  employeeId integer Primary Key Not Null,
  firstName varChar(20) null,
  lastName varChar(30) Not null,
  HireDate smallDateTime null,
  DepartmentId integer null
}

Тогда таблица History будет

Create Table EmployeeHistory
{
  employeeId integer Not Null,
  effectiveUtc DateTime Not Null,
  firstName varChar(20) null,
  lastName varChar(30) Not null,
  HireDate smallDateTime null,
  DepartmentId integer null,
  Primary Key (employeeId , effectiveUtc)
}

Затем вы можете поместить триггер в таблицу Employee, чтобы каждый раз, когда вы вставляете, обновляете или удаляете что-либо в таблице Employee, новая запись вставляется в таблицу EmployeeHistory с одинаковыми значениями для всех регулярных полей, и текущее время и время UTC в столбце effectiveUtc.

Затем, чтобы найти значения в любой точке в прошлом, вы просто выбираете запись из таблицы истории, значение effectiveUtc которой является наивысшим значением до того, как asOf datetime вы хотите получить значение с.

 Select * from EmployeeHistory h
 Where EmployeeId = @EmployeeId
   And effectiveUtc =
    (Select Max(effectiveUtc)
     From EmployeeHistory 
     Where EmployeeId = h.EmployeeId
        And effcetiveUtc < @AsOfUtcDate) 

Ответ 2

Чтобы добавить ответ Чарльза, я бы использовал модель Entity-Attribute-Value вместо создания другой таблицы истории для каждой другой таблицы в вашей базе данных.

По сути, вы бы создали одну таблицу History следующим образом:

Create Table History
{
  tableId varChar(64) Not Null,
  recordId varChar(64) Not Null,
  changedAttribute varChar(64) Not Null,
  newValue varChar(64) Not Null,
  effectiveUtc DateTime Not Null,
  Primary Key (tableId , recordId , changedAttribute, effectiveUtc)
}

Затем вы создадите запись History каждый раз, когда создаете или изменяете данные в одной из ваших таблиц.

Следуя вашему примеру, когда вы добавляете "Kyle" в таблицу Employee, вы создаете две записи (по одной для каждого атрибута без идентификатора), а затем создаете новую запись при каждом изменении свойства:

History 
+==========+==========+==================+==========+==============+
| tableId  | recordId | changedAttribute | newValue | effectiveUtc |
| Employee | 1        | Name             | Kyle     | N            |
| Employee | 1        | Property         | 30       | N            |
| Employee | 1        | Property         | 50       | N+1          |
| Employee | 1        | Property         | 70       | N+2          |

В качестве альтернативы, как предложено a_horse_with_no_name, если вы не хотите сохранять новую запись History для каждого изменения поля, вы можете сохранить сгруппированные изменения (такие как изменение Name на "Kyle" и Property на 30 в одном и том же обновлении) в виде одной записи, В этом случае вам нужно будет выразить коллекцию изменений в JSON или другом формате BLOB-объектов. Это приведет к объединению полей changedAttribute и newValue в одно (changedValues). Например:

History 
+==========+==========+================================+==============+
| tableId  | recordId | changedValues                  | effectiveUtc |
| Employee | 1        | { Name: 'Kyle', Property: 30 } | N            |

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

• добавление новых полей в таблицы в вашей базе данных не потребует добавления этих полей в другую таблицу
• меньше таблиц используется
• Проще соотносить обновления с разными таблицами с течением времени

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

Ответ 3

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

https://en.wikipedia.org/wiki/Slowly_changing_dimension

В Postgres 9.2 также не пропустите тип tsrange. Он позволяет объединить start_date и end_date в один столбец и индексировать материал индексом GIST (или GIN) наряду с ограничением исключения, чтобы избежать перекрытия диапазонов дат.

Edit:

должно быть понимание того, какие строки принадлежат одному и тому же "кластеру ревизии"

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

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

См. также этот связанный пост: Временный дизайн базы данных с твист (прямая трансляция строк)

Ответ 4

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

Подробная информация о реализации here.