sql - 通用数据库设计——我可以引用其他表中的字段名称吗

Question

我有一个表格，其中包含可以更新的各种字段。我们将其命名为 table1 并假设它具有以下字段:

user VARCHAR2
first_name VARCHAR2
last_name VARCHAR2

我的问题:我想跟踪对这些字段的编辑。我有一个允许更新这些字段的应用程序，但我想跟踪何时一个字段被编辑，谁编辑了该字段以及哪个 字段已编辑 - 是我需要帮助的部分。

我可以有一个历史表:

date_edited DATE
who_edited VARCHAR2
field_name_edited VARCHAR2

如果字段名称发生更改怎么办？..这意味着 field_name_edited 将引用一个不存在的字段。这似乎是一种愚蠢的方法。

必须有一些通用的方法来做这种事情吗？

非常感谢。

编辑

我正在使用 Oracle 数据库 - 查看新问题标签。

Answer 1

"There must be some common way of doing this sort of thing?"

不是真的，因为您的方法很常见但被误导了。由于以下几个原因，在列级别跟踪更改并不令人满意:

1. 当更新涉及多个列(为每个更新的行插入多个审计记录)时，它会变得昂贵。
2. 很难在给定的时间点组合出记录状态的连贯图片(编程复杂，启动成本更高)。
3. 没有优雅的方法来测试给定列是否已更改。
4. 存储列值很困惑(实现通常通过将它们转换为字符串来处理不同的数据类型)。

(您建议的表格没有处理最后一点，我认为这是一个疏忽:不跟踪更改值的审计跟踪没有什么值(value))。

那么，常见的解决方案是什么？行级历史表。使用将整个记录插入日记表的触发器(连同那些元数据列，如 DATE_EDITED 和 WHO_EDITED)。这些触发器可以很容易地从数据字典中生成。

的确，这种方法使得更难发现在任何给定事务中编辑了哪些列。然而:

1. 做到这一点很难，但并非不可能。它可以在 SQL 中实现(使用分析 LAG 或其他东西)，但实际上人类非常擅长在没有帮助的情况下发现变化。
2. 此成本由审计子系统承担，在现实生活中 - 无论需求如何 - 都不会使用太多，而且肯定比应用程序的核心部分少得多。因此，最好将处理成本转移到审计子系统上，而不是让日志记录变得昂贵。

而且，正如我所说，行级日记在生成审计记录和检索它们方面比列级日记产生的成本更低。

---

在您的评论中，您链接到一篇关于 Oracle 的 Total Recall product 的文章.这实际上是一个非常优雅的解决方案，它对主系统的影响非常小，并且可以很容易地恢复我们表的历史状态。问题是，在 12c 之前，它是企业版的额外费用，因此价格昂贵。 (事实上​​它现在是 Advanced Compression option 的一部分，而不是一个独立的产品)。在 12c Basic Flashback Data Archive(Total Recall 的新名称)中是 available in all editions , 但我们仍然需要购买 Advanced Compression Option 来压缩日志表。

---

"You recommend [to store] row edits and thus, store redundand data. "

是的。如今，存储通常很便宜。存储冗余数据的成本通常是为高效写入和检索记录付出的一个很好的代价。

" You still need to copy the literal names of the original columns"

Journalling 为每个数据表使用一个审计表；审计表与事件表的结构相匹配，有一些额外的列来保存与交易相关的元数据。我们希望审计列与其类似的数据列具有相同的名称。 (做任何其他事情都是愚蠢的，尤其是因为我们可以从数据字典中为审计表生成 DDL。)