Как сделать историю данных SQL? (сообщить об изменениях данных)

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

Правило 1: не встраивайте предположения в проект. Если что-то новое, оно должно быть помечено: «ЭЙ! Я здесь новенький!» Когда в данные были внесены изменения: «Хорошо, что-то изменилось. Вот оно». и когда проблема, наконец, была закрыта, «хорошо, это для меня. Я сделал для».

create table Bug_Static( -- Contains one entry for each bug
ID     int identity,
Opened date not null default sysdate,
Closed date [null | not null default date '9999-12-31'],
Title  varchar(...),
Who    id references Who_Table,
<other non-changing data>,
constraint PK_Bug_Static primary key( ID )
);
create table Bug_Versions( -- Contains changing data, like status
ID        int not null,
Effective date not null,
Status    varchar not null,  -- new,assigned,in work,closed,whatever
<other data that may change from day to day>,
constraint PK_Bug_Versions primary key( ID, Effective ),
constraint FK_Bug_Versions_Static foreign key( ID )
references Bug_Static( ID )
);

Теперь вы можете выбрать ошибки и текущие данные (последние внесенные изменения) в любой день.

select  s.ID, s.Opened, s.Title, v.Effective, v.Status
from    Bug_Static   s
join    Bug_Versions v
on  v.ID = s.ID
and v.Effective =(
select  Max( Effective )
from    Bug_Versions
where   ID = v.ID
and Effective <= sysdate )
where   s.Closed < sysdate;

where s.Closed < sysdate не является обязательным. Это дает вам все ошибки, которые были закрыты в день выполнения запроса, но не те, которые были закрыты до этого. Это предотвращает повторное появление закрытых ошибок — если только это не то, что вы хотите.

Изменить sysdate значения для конкретной даты / времени, и вы получите данные, как они появились на эту дату и время.

Обычно при создании ошибки строка вводится в обе таблицы. Тогда только новые версии вводятся как статус или любые другие изменения данных. Если за день ничего не изменилось, ничего не вводится. Затем, когда ошибка, наконец, закрыта, Closed поле статической таблицы обновляется и closed версия вставляется в таблицу версий. Я показал Closed поле с двумя опциями, нулевым или с определенной «максимальной датой» от 31 декабря 999 г. Вы можете использовать любой из них, но мне нравится метод максимальной даты. Это упрощает запросы.

Я также поставил бы перед обоими столами пару представлений, которые присоединяются к столам. Один, который показывает только последние версии каждой ошибки (Bug_Current) и один, который показывает каждую версию каждой ошибки (Bug_History). С триггерами на Bug_Current, это может быть тот, который используется приложением для изменения ошибок. Это изменило бы, например, обновление любого версионного поля на вставку новой версии.

Дело в том, что это очень гибкий дизайн, который вы можете легко показать только те данные, которые вы хотите, как вы хотите, в любое время.