Как лучше всего справляться с хранением исторических данных?

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

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

Должен ли я генерировать исторические данные в отдельной таблице "истории" и сохранять текущие данные только в "активной" таблице.

Если да, то как мне лучше всего это сделать? С триггером, который автоматически копирует данные в таблицу истории? Или с логикой в моем приложении?

Обновление за комментарий к Welbog:

Будет большое количество исторических данных (сотни тысяч строк - в конечном итоге потенциально миллионы)

В первую очередь операции поиска и отчетности будут выполняться по историческим данным.

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

Ответ 1

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

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

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

Ответ 2

Этот вопрос идет по линии Business Logic. Сначала знайте свои бизнес-требования, а затем начинайте оттуда. A Data Warehouse - отличное решение для такого рода ситуаций. ETL предоставит вам множество возможностей для обработки потоков данных. Ваша основная концепция "История" против "Активного" вполне правильна. Ваши исторические данные будут более эффективными и гибкими, если они хранятся в хранилище данных со всеми их размерами и таблицами фактов.