Лучший дизайн для таблицы базы данных changelog / auditing?

есть еще несколько вещей, которые вы можете проверить, такие как имена таблиц/столбцов, компьютер /приложение, из которого было сделано обновление и многое другое.

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

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

советы:

- включить значения до/после

-включить 3,4 столбца для хранения первичного ключа (в случае, если это составной ключ)

-хранить данные вне основной базы данных, как уже предложил Роберт

-потратьте приличное количество времени на подготовку отчетов – особенно тех, которые могут понадобиться для восстановления

-план хранения имени хоста / приложения – это может произойти очень полезно для отслеживания подозрительных действий

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

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

есть много интересных ответов здесь и в подобных вопросах. Единственное что я могу добавить из личного опыта...

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

2. Денормализовать как можно больше. Вы хотите, чтобы таблица имела как можно меньше зависимостей исходные данные. Таблица аудита должна быть простой и молниеносной для получения данных. Никаких причудливых соединений или поисков по другим таблицам, чтобы добраться до данных.

есть много способов сделать это. Мой любимый способ:

0-добавьте поле mod_user в исходную таблицу (ту, которую вы хотите зарегистрировать)

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

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

теперь у вас есть запись каждого изменения и кто его сделал.

что мы имеем в нашей таблице:-

Primary Key
Event type (e.g. "UPDATED", "APPROVED")
Description ("Frisbar was added to blong")
User Id
User Id of second authoriser
Amount
Date/time
Generic Id
Table Name

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

В общем пользовательский аудит (создание различных таблиц) - это плохой вариант. Триггеры базы данных / таблицы можно отключить, чтобы пропустить некоторые действия журнала. Пользовательские таблицы аудита могут быть изменены. Исключения могут иметь место, что приведет к падению приложения. Не говоря уже о трудностях проектирования надежного решения. До сих пор я вижу очень простые случаи в этой дискуссии. Вам необходимо полное отделение от текущей базы данных и от любых привилегированных пользователей(DBA, разработчиков). Каждый основной РСУБД обеспечивает аудит объекты, которые даже DBA не в состоянии отключить, подделать в тайне. Поэтому первым вариантом должна быть возможность аудита, предоставляемая поставщиком СУБД. Другим вариантом может быть стороннее средство чтения журнала транзакций или пользовательское средство чтения журнала, которое помещает разложенную информацию в систему обмена сообщениями, которая заканчивается в некоторых формах хранилища данных аудита или обработчика событий в реальном времени. Резюме: архитектор решений / "архитектор данных" должен участвовать в определении такой системы на основе требований. Это, как правило, тоже серьезные вещи просто передать разработчикам для решения.

согласно принципу разделения : -

1) Таблицы данных аудита должны быть отделены от основной базы данных. Потому что база данных аудита может иметь много исторических данных.

Так что имеет смысл от использования памяти также держать его отдельно.

2) Не используйте триггеры для аудита базы данных отверстий. Потому что вы в конечном итоге с беспорядком различной поддержки базы данных.Вы также должны написать один для DB2, SQLServer и Mysql и т. д.

поздно на вечеринку, но я очень рекомендую Автоаудит проект.
Это 100% бесплатно и с открытым исходным кодом. Он является автором по SQL Мвпс пол Нильсен и Джон Sigouin. Он очень стабилен и в настоящее время находится на версии 3.30.

простота установки. Просто запустите SP, который они предоставляют. Он создаст схему аудита, некоторые SPs обслуживания и триггеры, необходимые для проведения аудита. Оттуда просто выберите, какие таблицы вы хотите проверить и с какой детализацией.