Дизайн базы данных для пересмотра?

У нас есть требование в проекте хранить все изменения (История изменений) для объектов в базе данных. В настоящее время у нас есть 2 разработанных для этого предложения:

например, для организации «Сотрудник»

Дизайн 1:

-- Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" -- Holds the Employee Revisions in Xml. The RevisionXML will contain -- all data of that particular EmployeeId "EmployeeHistories (EmployeeId, DateModified, RevisionXML)" 

Дизайн 2:

 -- Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" -- In this approach we have basically duplicated all the fields on Employees -- in the EmployeeHistories and storing the revision data. "EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName, LastName, DepartmentId, .., ..)" 

Есть ли другой способ сделать это?

Проблема с «Design 1» заключается в том, что мы должны каждый раз анализировать XML, когда вам нужно получить доступ к данным. Это замедлит процесс и добавит некоторые ограничения, так как мы не можем добавлять объединения в поля данных ревизий.

И проблема с «Проектом 2» заключается в том, что мы должны дублировать каждое поле на всех объектах (у нас есть около 70-80 объектов, для которых мы хотим поддерживать ревизии).

    1. Не помещайте все это в одну таблицу с атрибутом descryptinator IsCurrent. Это просто вызывает проблемы на линии, требует суррогатных ключей и всевозможных других проблем.
    2. У проекта 2 есть проблемы с изменениями схемы. Если вы измените таблицу Employees, вы должны изменить таблицу EmployeeHistories и все связанные с ней sprocs, которые идут с ней. Потенциально удваивает усилия по смене схемы.
    3. Дизайн 1 работает хорошо, и если все сделано правильно, это не дорого стоит с точки зрения производительности. Вы можете использовать xml-схему и даже индексы, чтобы преодолеть возможные проблемы с производительностью. Ваш комментарий о разборе xml действителен, но вы можете легко создать представление с помощью xquery, которое вы можете включить в запросы и присоединиться к. Что-то вроде этого…
     CREATE VIEW EmployeeHistory AS , FirstName, , DepartmentId SELECT EmployeeId, RevisionXML.value('(/employee/FirstName)[1]', 'varchar(50)') AS FirstName, RevisionXML.value('(/employee/LastName)[1]', 'varchar(100)') AS LastName, RevisionXML.value('(/employee/DepartmentId)[1]', 'integer') AS DepartmentId, FROM EmployeeHistories 

    Я думаю, что ключевой вопрос здесь – «Кто / что собирается использовать историю»?

    Если это будет в основном для отчетности / удобочитаемой истории, мы реализовали эту схему в прошлом …

    Создайте таблицу под названием «AuditTrail» или что-то, что имеет следующие поля …

     [ID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NULL, [EventDate] [datetime] NOT NULL, [TableName] [varchar](50) NOT NULL, [RecordID] [varchar](20) NOT NULL, [FieldName] [varchar](50) NULL, [OldValue] [varchar](5000) NULL, [NewValue] [varchar](5000) NULL 

    Затем вы можете добавить столбец «LastUpdatedByUserID» ко всем вашим таблицам, которые следует устанавливать каждый раз, когда вы выполняете обновление / вставку в таблице.

    Затем вы можете добавить триггер для каждой таблицы, чтобы поймать любую вставку / обновление, которая происходит, и создает запись в этой таблице для каждого измененного поля. Поскольку таблица также снабжается «LastUpdateByUserID» для каждого обновления / вставки, вы можете получить доступ к этому значению в триггере и использовать его при добавлении в таблицу аудита.

    Мы используем поле RecordID для хранения значения поля ключа обновляемой таблицы. Если это комбинированный ключ, мы просто выполняем конкатенацию строк с «~» между полями.

    Я уверен, что эта система может иметь недостатки – для сильно обновляемых баз данных производительность может быть удачной, но для моего веб-приложения мы получаем гораздо больше чтений, чем записи, и, похоже, это работает очень хорошо. Мы даже написали небольшую утилиту VB.NET для автоматической записи триггеров на основе определений таблиц.

    Просто мысль!

    Статья « Таблицы истории» в блоге « Программист базы данных» может быть полезна – охватывает некоторые из поднятых здесь вопросов и обсуждает хранение дельт.

    редактировать

    В эссе « Столы истории» автор ( Кеннет Даунс ) рекомендует поддерживать таблицу истории по меньшей мере из семи столбцов:

    1. Временная отметка об изменении,
    2. Пользователь, внесший изменения,
    3. Токен для идентификации записи, которая была изменена (где история поддерживается отдельно от текущего состояния),
    4. Было ли изменение вставкой, обновлением или удалением,
    5. Старое значение,
    6. Новое значение,
    7. Дельта (для изменения числовых значений).

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

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

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

    Единственное отличие состоит в том, что мы сохраняем только новое значение. Старое значение сохраняется в предыдущей строке истории

     [ID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NULL, [EventDate] [datetime] NOT NULL, [TableName] [varchar](50) NOT NULL, [RecordID] [varchar](20) NOT NULL, [FieldName] [varchar](50) NULL, [NewValue] [varchar](5000) NULL 

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

    Если вам нужно сохранить историю, создайте теневую таблицу с той же схемой, что и отслеживаемая таблица, а также столбец «Дата ревизии» и «Тип пересмотра» (например, «удалить», «обновить»). Напишите (или сгенерируйте – см. Ниже) набор триггеров для заполнения таблицы аудита.

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

    Не пытайтесь использовать XML для этого, хранилище XML намного менее эффективно, чем хранилище таблиц собственной базы данных, которое использует этот тип триггера.

    Избегайте дизайна 1; это не очень удобно, если вам понадобится, например, откат к старым версиям записей – либо автоматически, либо «вручную» с помощью консоли администратора.

    Я не вижу недостатков дизайна 2. Я думаю, что вторая таблица истории должна содержать все столбцы, присутствующие в первой таблице записей. Например, в mysql вы можете легко создать таблицу с той же структурой, что и другая таблица ( create table X like Y ). И когда вы собираетесь изменить структуру таблицы Records в своей живой базе данных, вам все равно придется использовать команды alter table – и нет никаких больших усилий при выполнении этих команд также для вашей таблицы History.

    Заметки

    • Таблица записей содержит только последнюю ревизию;
    • Таблица истории содержит все предыдущие изменения записей в таблице записей;
    • Основной ключ таблицы истории – это первичный ключ таблицы Records с добавленным столбцом RevisionId ;
    • Подумайте о дополнительных вспомогательных областях, таких как ModifiedBy – пользователь, который создал конкретную ревизию. Вы также можете иметь поле DeletedBy для отслеживания того, кто удалил конкретную ревизию.
    • Подумайте о том, что должно означать DateModified – либо это означает, что эта конкретная ревизия была создана, либо это будет означать, когда эта конкретная ревизия была заменена другой. Первый требует, чтобы поле находилось в таблице записей, и кажется на первый взгляд более интуитивным; второе решение, однако, представляется более практичным для удаленных записей (дата, когда эта конкретная ревизия была удалена). Если вы пойдете на первое решение, вам, вероятно, понадобится второе поле DateDeleted (только если вам это нужно, конечно). Зависит от вас и того, что вы на самом деле хотите записать.

    Операции в дизайне 2 очень тривиальны:

    изменять

    • скопируйте запись из таблицы записей в таблицу History, дайте ей новый RevisionId (если он еще не присутствует в таблице записей), обработайте DateModified (в зависимости от того, как вы его интерпретируете, см. примечания выше)
    • продолжить обычное обновление записи в таблице записей

    Удалить

    • сделайте то же самое, что и на первом этапе операции «Изменить». Соответственно, обрабатывается DateModified / DateDeleted, в зависимости от выбранной вами интерпретации.

    Отменить (или откат)

    • взять самую высокую (или некоторую конкретную?) версию из таблицы «История» и скопировать ее в таблицу «Записи»

    Перечислить историю изменений для определенной записи

    • выберите из таблицы «История» и «Таблица записей»
    • подумайте, что именно вы ожидаете от этой операции; он, вероятно, определит, какую информацию вы требуете от полей DateModified / DateDeleted (см. примечания выше)

    Если вы идете на Design 2, все команды SQL, необходимые для этого, будут очень легкими, а также обслуживанием! Возможно, это будет намного проще, если вы используете вспомогательные столбцы ( RevisionId , DateModified ) также в таблице Records – чтобы обе таблицы были в одинаковой структуре (за исключением уникальных ключей)! Это позволит использовать простые команды SQL, которые будут толерантны к изменению структуры данных:

     insert into EmployeeHistory select * from Employe where ID = XX 

    Не забудьте использовать транзакции!

    Что касается масштабирования , это решение очень эффективно, поскольку вы не преобразуете данные из XML взад и вперед, просто копируя целые строки таблицы – очень простые запросы, используя индексы – очень эффективны!

    Рамеш, я участвовал в разработке системы, основанной на первом подходе.
    Оказалось, что хранение изменений в XML приводит к огромному росту базы данных и значительно замедляет работу.
    Мой подход состоит в том, чтобы иметь одну таблицу для каждого объекта:

     Employee (Id, Name, ... , IsActive) 

    где IsActive является признаком последней версии

    Если вы хотите связать некоторую дополнительную информацию с ревизиями, вы можете создать отдельную таблицу, содержащую эту информацию, и связать ее с таблицами сущностей, используя отношение PK \ FK.

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

    • Простая структура базы данных
    • Нет конфликтов, поскольку таблица становится только append
    • Вы можете вернуться к предыдущей версии, просто изменив флаг IsActive
    • Нет необходимости в соединениях для получения истории объектов

    Обратите внимание, что вы должны разрешить первичному ключу быть уникальным.

    То, как я видел это в прошлом,

     Employees (EmployeeId, DateModified, < Employee Fields > , boolean isCurrent ); 

    Вы никогда не «обновляете» эту таблицу (за исключением изменения значения isCurrent), просто вставьте новые строки. Для любого данного EmployeeId только 1 строка может иметь isCurrent == 1.

    Сложность сохранения этого может быть скрыта с помощью представлений и «вместо« триггеров »(в oracle, я предполагаю, что у других подобных RDBMS), вы даже можете перейти к материализованным представлениям, если таблицы слишком большие и не могут обрабатываться индексами) ,

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

    Лично я очень люблю ваш дизайн 2-го способа сделать это, как я это делал и в прошлом. Его просто понять, просто реализовать и прост в обслуживании.

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

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

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

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

    Пересмотр данных является аспектом концепции « действительного времени » временной базы данных. Много исследований было уделено этому, и многие модели и руководящие принципы появились. Я написал длинный ответ с кучей ссылок на этот вопрос для тех, кого это интересует.

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

    Однако я бы попробовал второй подход. Вы можете упростить это, имея только одну таблицу Employees с полем DateModified. EmployeeId + DateModified будет основным ключом, и вы можете сохранить новую ревизию, просто добавив строку. Таким образом, легче архивировать старые версии и восстанавливать версии из архива.

    Другим способом сделать это может быть модель datavault от Dan Linstedt. Я сделал проект для голландского статистического бюро, который использовал эту модель, и она работает достаточно хорошо. Но я не думаю, что это полезно для повседневной работы с базами данных. Вы могли бы получить некоторые идеи от чтения его бумаг.

    Я собираюсь поделиться с вами своим дизайном, и он отличается от обоих ваших проектов тем, что для каждой сущности требуется одна таблица. Я нашел лучший способ описать любой дизайн базы данных через ERD, вот мой:

    введите описание изображения здесь

    В этом примере у нас есть объект с именем employee . таблица пользователя содержит записи ваших пользователей, а entity и entity_revision – две таблицы, содержащие историю изменений для всех типов сущностей, которые вы будете иметь в своей системе. Вот как работает этот проект:

    Два поля объекта entity_id и revision_id

    Каждый объект в вашей системе будет иметь уникальный идентификатор объекта. Ваша организация может пройти ревизии, но ее entity_id останется прежним. Вы должны сохранить этот идентификатор объекта в своей таблице сотрудников (в качестве внешнего ключа). Вы также должны сохранить тип своего объекта в таблице сущностей (например, «сотрудник»). Теперь, что касается version_id, как показывает его имя, он отслеживает ваши сущности. Лучший способ, который я нашел для этого, – использовать employee_id в качестве вашей ревизии_ид. Это означает, что у вас будут повторяющиеся идентификаторы ревизий для разных типов объектов, но это не относится ко мне (я не уверен в вашем случае). Важно только отметить, что комбинация объектов entity_id и revision_id должна быть уникальной.

    Также есть поле состояния в таблице entity_revision, которое указывает состояние пересмотра. Он может иметь одно из трех состояний: latest , obsolete или deleted (не полагаясь на дату пересмотра, поможет вам значительно повысить ваши запросы).

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

    INSERTION

    Для каждого сотрудника, который вы хотите вставить в базу данных, вы также добавите запись в entity и entity_revision . Эти последние две записи помогут вам отслеживать, кем и когда запись была вставлена ​​в базу данных.

    ОБНОВИТЬ

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

    УДАЛЕНИЕ

    Для удаления сотрудника запись вставляется в entity_revision с указанием удаления и выполнения.

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

    [ОБНОВИТЬ]

    Поддержив разделы в новых версиях MySQL, я считаю, что мой дизайн также имеет один из лучших исполнений. Можно разбивать таблицу entity используя поле type то время как раздел entity_revision использует поле state . Это значительно увеличит запросы SELECT , сохраняя при этом дизайн простым и чистым.

    Как насчет:

    • EmployeeID
    • Дата изменена
      • и / или номер ревизии, в зависимости от того, как вы хотите отслеживать его
    • ModifiedByUSerId
      • плюс любую другую информацию, которую вы хотите отслеживать
    • Поля сотрудников

    Вы делаете первичный ключ (EmployeeId, DateModified), и для получения «текущей» записи (ов) вы просто выбираете MAX (DateModified) для каждого employeeid. Хранение IsCurrent – очень плохая идея, потому что, во-первых, она может быть вычислена, а во-вторых, слишком сложно для данных выйти из синхронизации.

    Вы также можете сделать представление, в котором перечислены только последние записи, и в основном использовать это при работе в вашем приложении. Самое приятное в этом подходе заключается в том, что у вас нет дубликатов данных, и вам не нужно собирать данные из двух разных мест (текущие в Employees и архивированные в EmployeesHistory), чтобы получить всю историю или откат и т. Д.). ,

    Если вы хотите полагаться на данные истории (для объяснения причин), вы должны использовать такую ​​структуру:

     // Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds the Employee revisions in rows. "EmployeeHistories (HistoryId, EmployeeId, DateModified, OldValue, NewValue, FieldName)" 

    Или глобальное решение для применения:

     // Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds all entities revisions in rows. "EntityChanges (EntityName, EntityId, DateModified, OldValue, NewValue, FieldName)" 

    Вы можете сохранить свои изменения также в XML, тогда у вас есть только одна запись для одной ревизии. Это будет выглядеть так:

     // Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds all entities revisions in rows. "EntityChanges (EntityName, EntityId, DateModified, XMLChanges)" 

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

    Я не уверен, что такое ваш прецедент, но мы сделали таблицу создания и аудита, которая автоматически обновляется с изменениями в бизнес-сущности, включая дружественное имя любых ссылок на внешние ключи и перечисления.

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

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

    Похоже, вы хотите отслеживать изменения для определенных объектов с течением времени, например, ID 3, «bob», «123 main street», затем еще один ID 3, «bob» «234 elm st» и т. Д., По существу, способный чтобы просмотреть историю изменений, показывающую, что каждый адрес «bob» был включен.

    Лучший способ сделать это – иметь «текущее» поле для каждой записи и (возможно) временную метку или FK для таблицы даты / времени.

    Вставки должны затем установить «is current», а также отключить «current» в предыдущей «текущей» записи. Запросы должны указывать «текущий», если вы не хотите всю историю.

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