使用数据库行存储有关行的元数据？

Question

关于存储一行元数据的最佳实践是什么？

以银行间金融转账为例。 Transfer 可能看起来像：

CREATE TABLE Transfers (
   TransferID int,
   FromTransit varchar(10),
   FromBranch varchar(10),
   FromAccount varchar(50),
   ToTransit varchar(10),
   ToBranch varchar(10),
   ToAccount varchar(50),
   Amount money,
   Status varchar(50));

但是现在，人们当然希望看到元数据：

ALTER TABLE Transfers 
ADD
    CreatedDate datetime,
    LastModifiedDate datetime,
    CreatedByUsername varchar(50),
    CreatedByFullname varchar(200),
    CreatedByWorkstation varchar(50),

    VoidedDate datetime NULL,
    VoidedByUsername datetime NULL,
    VoidedByFullname datetime NULL,
    VoidApprovedBySupervisorUsername varchar(50) NULL,
    VoidApprovedBySupervisorFullname varchar(200) NULL,
    VoidApprovedBySupervisorWorkstation varchar(50) NULL,

    SentDate datetime NULL, 
    SentByUsername varchar(50) NULL,
    SentByFullname varchar(50) NULL,
    SentByWorkstation varchar(50) NULL,
    SendApprovedBySupervisorUsername varchar(50) NULL,
    SendApprovedBySupervisorFullname varchar(50) NULL,
    SendApprovedBySupervisorWorkstation varchar(50) NULL,
    SendConfirmationNumber varchar(50) NULL,
    SentToRemoteMachineName varchar(50) NULL,

    ReceivedDate datetime NULL, 
    ReceivedConfirmationNumber varchar(50) NULL,
    ReceivedToRemoteMachineName varchar(50) NULL,
    ReceivedByUsername varchar(50) NULL,
    ReceivedByFullname varchar(50) NULL,
    ReceivedByWorkstation varchar(50) NULL,
    ReceiveApprovedBySupervisorUsername varchar(50) NULL,
    ReceiveApprovedBySupervisorFullname varchar(50) NULL,
    ReceivedApprovedBySupervisorWorkstation varchar(50) NULL,

    ReceivedCheckedBySupervisorUsername varchar(50) NULL,
    ReceivedCheckedBySupervisorFullname varchar(50) NULL,
    ReceivedCheckedBySupervisorWorkstation varchar(50) NULL
)

这些都是定义明确的值，它们都将出现在与传输相关的硬拷贝上。

我们已经对表中的更改进行了审核日志记录，但这不会捕获类似以下内容：

UPDATE Transfers SET Status = 'TransferStatus_Received'
WHERE TransferID = 6744891

它会捕获用户名、全名和机器名称 > 做出改变的人；但它无法知道负责输入凭据以“授权”接收转账的主管的姓名。

当他们要求跟踪另一条信息时，我的烦恼就来了，我必须向我的数据表中添加更多的元数据列。

这是最佳实践吗？

Answer 1

对于金融数据库来说，这不是一个好的做法，因为您允许更新。如果您允许更新，那么您添加什么日志记录、审计、加密密钥或任何内容都无关紧要，因为敌对方可以直接更新它们。

相反，您必须禁止更新；所有更改都必须是插入。所有表都应该有一个带索引的顺序 FK 列，并且所有连接都在 Max(seq) 上。这意味着您对最新数据执行所有事务，但在这些表上拥有每个事务的永久记录。

编辑：如果您询问的是是否应该将审核列添加到原始表中，这取决于审核列是否稀疏或可为空。从你的评论来看，似乎是这样。

在这种情况下，您应该为每个可为空的审核属性组创建单独的表，并对这些表执行外连接，与原始数据库的顺序列连接。这意味着您可以随意添加或删除审计表，而不会影响您的数据表。例如：

SELECT t.transferID, t.money, u.Date, u.workstation, s.name, ...
FROM Transfers t
    LEFT OUTER JOIN Users u ON u.seq = t.seq
    LEFT OUTER JOIN Supervisors s ON s.seq = t.seq
WHERE t.seq = (SELECT Max(seq) FROM Transfers WHERE whatever)

如果您需要在事务中重用它，您可以创建一个视图或存储过程来保存 Max(seq) 。

Answer 2

我对 SQL Server 不太了解，但是当在 Oracle 场景中遇到这种情况时，我倾向于使用触发器（插入/更新/删除），它将完整的行（之前和之后）放入“归档/审核”表中并添加他们想要记录的任何“元数据”...这样我的以应用程序为中心的数据模型就不会受到应用程序/SP等的污染，并且没有应用程序/用户可以访问敏感的记录/审计信息...