База данных - управление версиями данных

Я использовал различные схемы аудита на протяжении многих лет, и в настоящее время я собираюсь реализовать что-то вроде этого:

Person
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL


Person_History
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL
AuditID           UINT NOT NULL


Audit
------------------------------------------------
ID                UINT NOT NULL,
UserID            UINT NOT NULL,               -- Who
AffectedOn        DATE NOT NULL,               -- When
Comment           VARCHAR(500) NOT NULL        -- Why

Текущие записи всегда находятся в таблице Person. Если есть изменение, создается запись аудита, а старая запись копируется в таблицу Person_History (обратите внимание, что идентификатор не меняется, и может быть несколько версий)

Идентификатор аудита находится в таблицах * _History, поэтому при желании вы можете связать несколько изменений записей с одной записью аудита.

РЕДАКТИРОВАТЬ:
Если у вас нет отдельной таблицы истории для каждой базовой таблицы и вы хотите использовать одну и ту же таблицу для хранения старых и «удаленных» записей, тогда вы должны пометить записи флагом состояния. Проблема в том, что это настоящая боль при запросе текущих записей - поверьте мне, я это сделал.

Как насчет того, чтобы создать таблицу как обычно, иметь столбец ModifiedDate для каждой записи (и ModifiedBy, если хотите) и обеспечить доступ ко всем данным через материализованное представление, которое группирует данные по идентификатору, а затем выполняет HAVING ModifiedDate = MAX (ModifiedDate )?

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

Я всегда считал, что поддерживать разные таблицы с одним и тем же Colm сложно и подвержено ошибкам.

edit: Я только что вернулся к этому ответу через 12 лет после того, как написал его. Я бы сказал, что исходный вопрос неверен - вы должны проверять события на уровне пользователя, а не изменения столбцов базы данных.

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

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

Я думал, что мне нужно будет выполнить выборку SQL для загрузки записи из базовой таблицы, а затем выполнить SQL-запрос, чтобы поместить запись в таблицу истории, а затем обновить базовую таблицу для вставки измененных данных. Всего 3 транзакции.

Но, к своему удивлению, я понял, что вы можете выполнять первые две транзакции, используя один оператор SQL, используя синтаксис SELECT INTO. Бьюсь об заклад, при этом производительность будет в сотни раз выше.

Тогда это оставит нас просто ОБНОВИТЬ запись новыми данными в базовой таблице.

Я до сих пор не нашел ни одного оператора SQL для выполнения всех трех транзакций одновременно (сомневаюсь, что найду).

Мне нравится ваша таблица аудита, это хорошее начало. У вас проблема с количеством элементов в вашей таблице аудита, поэтому я бы разделил ее на две таблицы:

Person
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL,
AuditID           UINT NOT NULL 

Audit
------------------------------------------------
ID                UINT NOT NULL,
TableName         VARCHAR(50) NOT NULL,        -- What
TableKey          UINT NOT NULL,
CreateDate        DATETIME NOT NULL  DEFAULT(NOW),
CreateUserID      UINT NOT NULL,
ChangeDate        DATETIME NOT NULL  DEFAULT(NOW),
ChangeUserID      UINT NOT NULL

Audit_Item
------------------------------------------------
ID                UINT NOT NULL,
AuditID           UINT NOT NULL,               -- Which audit record
UserID            UINT NOT NULL,               -- Who
OldRecID          UINT NOT NULL,               -- Where
NewRecID          UINT NOT NULL,
AffectedOn        DATE NOT NULL,               -- When
Comment           VARCHAR(500) NOT NULL        -- Why

Первоначальный предложенный макет имеет единственную запись аудита, которая указывает на (я полагаю) две записи Person. Проблемы этой конструкции:

• Какие записи в вашей таблице людей являются текущими «настоящими» записями?
• Как вы представляете всю историю изменений в записи Person? Если вы указываете на две записи в таблице Person, см. Пункт №1: какая из них является текущей записью?
• Поля Create *, Change * сводятся из коллекции записей Audit_Item. Они есть только для удобства доступа.
• Ключ AuditID в таблице Person позволяет вам вернуться к таблице аудита и получить доступ к истории отдельного человека без необходимости запрашивать таблицу аудита с предложением WHERE TableName='Person'