Например, у меня есть таблица, в которой хранятся сведения о свойствах. Которые могут иметь владельцев, ценность и т.д.
Есть ли хороший дизайн, чтобы сохранить историю каждого изменения владельца и ценность. Я хочу сделать это для многих таблиц. Подобно аудиту таблицы.
То, что я думал, содержало одну таблицу с полями
table_name, field_name, prev_value, current_val, time, user.
Но он выглядит вроде хаки и уродливый. Есть ли лучший дизайн?
Спасибо.
Существует несколько подходов
Основанный на поле
audit_field (table_name, id, field_name, field_value, datetime)
Это позволяет фиксировать историю всех таблиц и легко распространяться на новые таблицы. Для новых таблиц не требуется никаких изменений в структуре.
Field_value иногда разделяется на несколько полей, чтобы поддерживать фактический тип поля из исходной таблицы (но только одно из этих полей будет заполнено, поэтому данные денормализуются; вариант состоит в том, чтобы разбить приведенную выше таблицу на одну таблицу для каждый тип).
Другие метаданные, такие как field_type, user_id, user_ip, действие (обновление, удаление, вставка) и т.д. могут быть полезны.
Структура таких записей, скорее всего, должна быть преобразована для использования.
Запись на основе
audit_table_name (timestamp, id, field_1, field_2, ..., field_n)
Для каждого типа записи в базе данных создайте обобщенную таблицу, в которой есть все поля в качестве исходной записи, плюс поле версии (возможны дополнительные метаданные). Необходима одна таблица для каждой рабочей таблицы. Процесс создания таких таблиц может быть автоматизирован.
Этот подход предоставляет вам семантически богатую структуру, очень похожую на основную структуру данных, поэтому инструменты, используемые для анализа и обработки исходных данных, также могут быть легко использованы в этой структуре.
Файл журнала
Первые два подхода обычно используют таблицы, которые очень легко индексируются (или вообще не содержат индексов и не имеют ссылочной целостности), так что штраф за запись минимизируется. Тем не менее, иногда может быть предпочтительным файл с плоским журналом, но, конечно, функционально значительно сокращается. (В основном зависит, хотите ли вы, чтобы фактический аудит/журнал, который будет анализироваться какой-либо другой системой, или исторические записи являются частью основной системы).
Другой способ взглянуть на это - это время-размер данных.
Предполагая, что ваша таблица выглядит так:
create table my_table (
my_table_id number not null primary key,
attr1 varchar2(10) not null,
attr2 number null,
constraint my_table_ak unique (attr1, att2) );
Затем, если вы изменили его так:
create table my_table (
my_table_id number not null,
attr1 varchar2(10) not null,
attr2 number null,
effective_date date not null,
is_deleted number(1,0) not null default 0,
constraint my_table_ak unique (attr1, att2, effective_date)
constraint my_table_pk primary key (my_table_id, effective_date) );
Вы сможете иметь полную историю работы my_table, онлайн и доступную. Вам нужно будет изменить парадигму программ (или использовать триггеры базы данных) для перехвата активности UPDATE в активность INSERT и изменить DELETE-активность в UPDATing логическое значение IS_DELETED.
неразумие:
Вы правы, что это решение аналогично аудиту на основе записей; Сначала я читал его как конкатенацию полей в строку, которую я также видел. Мои извинения.
Основные различия, которые я вижу между временем, определяющим таблицу, и использованием аудиторского центра, основанного на записи, вокруг ремонтопригодности, не жертвуя производительностью или масштабируемостью.
Поддержание работоспособности: Следует помнить об изменении таблицы теневого дерева при внесении структурных изменений в основную таблицу. Точно так же нужно помнить о внесении изменений в триггеры, которые выполняют отслеживание изменений, поскольку такая логика не может жить в приложении. Если вы используете представление для упрощения доступа к таблицам, вы также должны его обновить и изменить вместо этого триггер, который будет против него, чтобы перехватить DML.
В таблице с учетом времени вы делаете необходимые вам структурные изменения, и все готово. Как кто-то, кто был FNG в устаревшем проекте, такая ясность ценится, особенно если вам нужно много рефакторинга.
Производительность и масштабируемость:. Если одна из разделов таблицы с указанием времени в столбце даты фактической/истечения срока действия, активные записи находятся в одной "таблице", а неактивные записи - в другой. Насколько это менее масштабируемо, чем ваше решение? "Удаление" и активная запись включают перемещение строк в Oracle, которое является элементом удаления и вставки под обложками - именно то, что потребует решение на основе записи.
Отказоустойчивость заключается в том, что если приложение запрашивает запись по состоянию на некоторую дату, устранение разделов позволяет базе данных искать только таблицу/индекс, где может быть запись; решение на основе поиска для поиска активных и неактивных записей потребует UNION-ALL, и использование такого представления не требует повселокального использования UNION-ALL или использования какой-либо логики "look-here, then look-there" в приложение, о котором я говорю: blech.
Короче говоря, это выбор дизайна; Я не уверен, правильно или неправильно.
В наших проектах мы обычно делаем это так: У вас есть таблица
properties(ID, value1, value2)
то вы добавляете таблицу
properties_audit(ID, RecordID, timestamp or datetime, value1, value2)
ID - это идентификатор записи истории (не требуется)
RecordID - указывает на запись в исходной таблице свойств.
когда вы обновляете таблицу properties, вы добавляете новую запись в properties_audit с предыдущими значениями записи, обновленными в properties. Это можно сделать с помощью триггеров или в вашем DAL.
После этого у вас есть последнее значение в properties и вся история (предыдущие значения) в properties_audit.
Я думаю, что более простая схема будет
table_name, field_name, value, time, userId
Не нужно сохранять текущие и предыдущие значения в таблицах аудита. Когда вы вносите изменения в любое из полей, вам просто нужно добавить строку в таблицу аудита с измененным значением. Таким образом, вы всегда можете сортировать таблицу аудита вовремя и знать, каково было предыдущее значение в поле до вашего изменения.