审核记录的数据库设计 [英] Database design for audit logging

问题描述

每次我需要一个新的数据库，我花了相当一段时间
考虑如何设置数据库模式以保持审核日志
的更改。

有些问题已经在这里被问到了，但是我不同意
在所有场景中都有一个最好的方法：

• 修订版数据库设计


• 更改日志审核数据库表的最佳设计


• 想法用于捕获审计跟踪的数据库设计

我也绊倒了这个有趣的文章维护数据库更改日志，试图列出每种方法的专业和缺点。这是非常好的书面和有趣的信息，但它使我的决定更加困难。

我的问题是：有一个参考，我可以使用，也许是一本书或类似决策树的东西，我可以参考决定我应该根据一些
输入变量去哪一种方式，如：

• 数据库架构的成熟度


• 如何查询日志


• 需要重新创建的概率记录


• 更重要的是：写或读性能


• 正在记录的值的性质（字符串，数字，blob）


• 可用的存储空间

我知道的方法是：

1。添加创建和修改日期和用户的列

表示例：

• id


• value_1


• value_2


• value_3


• created_date


• modifed_date


• created_by


• modified_by

主要缺点：我们失去修改的历史。提交后无法回滚。

2。仅插入表

表示例：

• id


• $_1
  
• value_2


• value_3


• 从




• 已删除（布尔值）


• 用户

主要缺点：如何让外键保持最新？需要巨大的空间

3。为每个表创建一个单独的历史记录表

历史表示例：

• id


• value_1


• value_2


• value_3


• 
  
  

  主要缺点：需要重复所有审核的表。如果模式发生变化，则需要迁移所有日志。

  
  
  

  4。创建所有表的合并历史表

  
  
  

  历史表示例：

  
  
  
  
  
  • table_name
  
  
  • 字段
  
  
  • 用户
  
  
  • new_value
  
  
  • deleted（boolean）
  
  
  • timestamp
  
  
  
  
  

  主要缺点：我能否重新创建记录（回滚）如果需要容易？ new_value列需要是一个巨大的字符串，因此它可以支持所有不同的列类型。

  解决方案

  几个维基平台是分离识别数据和您正在审核的内容。它增加了复杂性，但是您最终会收到完整记录的审计跟踪，而不仅仅是编辑过的字段的列表，然后您必须进行补充，才能让用户了解旧记录的样子。

  
  
  

  所以例如，如果你有一个名为 Opportunities 的表跟踪销售交易，那么你实际上会创建两个单独的表：

  
  
  

  机会
  
  Opportunities_Content （或类似的内容）

  
  
  

  机会表格将包含用于唯一标识记录的信息，并将为您的外键关系提供主键。 Opportunities_Content 表将包含用户可以更改的所有字段，并且您希望保留审计跟踪。 内容表中的每条记录都将包含自己的PK和修改后的修改日期数据。 机会表将包括对当前版本的引用以及主记录何时创建的信息以及由谁创建的信息。

  
  
  

  这里是一个简单的例子：

    CREATE TABLE dbo.Page（
   ID int PRIMARY KEY，
   Name nvarchar 200）NOT NULL，
   CreatedByName nvarchar（100）NOT NULL，
   CurrentRevision int NOT NULL，
   CreatedDateTime datetime NOT NULL 
    

  并且内容：

    CREATE TABLE dbo.PageContent（
   PageID int NOT NULL，
  修订版int NOT NULL，
  标题nvarchar（200）NOT NULL，
  用户nvarchar（100）NOT NULL，
   LastModified datetime NOT NULL，
  注释nvarchar（300）NULL，
  内容nvarchar（max）NOT NULL，
  描述nvarchar（200）NULL 
    

  我可能会使内容表的PK成为来自PageID和修订版本的多列键，修订版是一种身份类型。您将使用版本列作为FK。然后，您可以通过JOINing拉动合并记录：

    SELECT * FROM Page 
   JOIN PageContent ON CurrentRevision = ID = PageID 
    

  可能有一些错误在那里...这是我头顶。它应该给你一个替代模式的想法。

  
  
  

  Josh

  
  

  Every time I need to desing a new database I spend quite some time thinking on how I should set up the database schema to keep an audit log of the changes.

  Some questions have already been asked here about this, but I don't agree that there is a single best approach for all scenarios:

  • Database Design For Revisions
  • Best design for a changelog auditing database table
  • Ideas on database design for capturing audit trails

  I have also stumbled upon this interesting article on Maintaining a Log of Database Changes that tries to list the pro and cons of each approach. It's very well written and has interesting information, but it has made my decisions even harder.

  My question is: Is there a reference that I can use, maybe a book or something like a decision tree that I can refer to decide which way should I go based on some input variables, like:

  • The maturity of the database schema
  • How the logs will be queried
  • The probability that it will be need to recreate records
  • What's more important: write or read performance
  • Nature of the values that are being logged (string, numbers, blobs)
  • Storage space available

  The approaches that I know are:

  1. Add columns for created and modified date and user

  Table example:

  • id
  • value_1
  • value_2
  • value_3
  • created_date
  • modifed_date
  • created_by
  • modified_by

  Major cons: We lose the history of the modifications. Can't rollback after commit.

  2. Insert only tables

  Table example:

  • id
  • value_1
  • value_2
  • value_3
  • from
  • to
  • deleted (boolean)
  • user

  Major cons: How to keep foreign keys up to date? Huge space needed

  3. Create a Separate history table for each table

  History table example:

  • id
  • value_1
  • value_2
  • value_3
  • value_4
  • user
  • deleted (boolean)
  • timestamp

  Major cons: Needs to duplicate all audited tables. If the schema changes it will be needed to the migrate all the logs too.

  4. Create a Consolidated history Table for All Tables

  History table example:

  • table_name
  • field
  • user
  • new_value
  • deleted (boolean)
  • timestamp

  Major cons: Will I be able to recreate the records (rollback) if needed easily? The new_value column needs to be a huge string so it can support all different column types.

  解决方案

  One method that is used by a few wiki platforms is to separate the identifying data and the content you're auditing. It adds complexity, but you end up with an audit trail of complete records, not just listings of fields that were edited that you then have to mash up to give the user an idea of what the old record looked like.

  So for example, if you had a table called Opportunities to track sales deals, you would actually create two separate tables:

  Opportunities
  Opportunities_Content (or something like that)

  The Opportunities table would have information you'd use to uniquely identify the record and would house the primary key you'd reference for your foreign key relationships. The Opportunities_Content table would hold all the fields your users can change and for which you'd like to keep an audit trail. Each record in the Content table would include its own PK and the modified-by and modified-date data. The Opportunities table would include a reference to the current version as well as information on when the main record was originally created and by whom.

  Here's a simple example:

  CREATE TABLE dbo.Page(  
      ID int PRIMARY KEY,  
      Name nvarchar(200) NOT NULL,  
      CreatedByName nvarchar(100) NOT NULL, 
      CurrentRevision int NOT NULL, 
      CreatedDateTime datetime NOT NULL
  

  And the contents:

  CREATE TABLE dbo.PageContent(
      PageID int NOT NULL,
      Revision int NOT NULL,
      Title nvarchar(200) NOT NULL,
      User nvarchar(100) NOT NULL,
      LastModified datetime NOT NULL,
      Comment nvarchar(300) NULL,
      Content nvarchar(max) NOT NULL,
      Description nvarchar(200) NULL
  

  I would probably make the PK of the contents table a multi-column key from PageID and Revision provided Revision was an identity type. You would use the Revision column as the FK. You then pull the consolidated record by JOINing like this:

  SELECT * FROM Page
  JOIN PageContent ON CurrentRevision = Revision AND ID = PageID
  

  There might be some errors up there...this is off the top of my head. It should give you an idea of an alternative pattern, though.

  Josh

  这篇关于审核记录的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！