postgresql - 设计数据库表的最佳实践是什么

Question

我们正在构建我们的数据库设计(使用 PostgreSQL)并且对于(几乎)每个表，我都有以下列

CREATE_TIMESTAMP TIMESTAMP,
CREATED_BY       VARCHAR(25),
modified_TIMESTAMP TIMESTAMP,
modified_BY       VARCHAR(25),

我还在为一些实体表使用审计表。截至目前，数据库大约有 15 个表(很快将增长到大约 50 个)。对于大约 20% 的这些表(它们是实体)，我们需要将它们备份(使用触发器)到审计表的相同副本。例如:一个家庭有 1 个或多个“联系人”。联系人有电子邮件、电话等信息加上 1 个“地址”。因此，当一个族被创建、修改或删除时，我使用触发器将族表的内容复制到它的审计表 Family_Audit。同样，当对“Contact”表进行更改时，我会将其复制到 Contact_Audit 表。地址也一样。

变更日志表:如果我已经为每个需要“审计”的实体表创建了审计表，那么拥有变更日志表有什么意义？

鉴于此，我想知道使用上述“样板”列对我来说是否仍然有意义。

有什么意见吗？

更重要的是，您添加到(几乎)每个 Table 的样板列是什么？为什么？

Answer 1

我尽量避免“样板列”。

如果你想要一个更改日志，创建一个 ChangeLog 表，在日志中包含用户名、时间戳、表名和表行 ID，而不是在表上。

唯一接近“样板”的是代理主键(称为 ID)。

在大多数情况下，“样板”——更改历史——甚至都不是问题，因为我试图创建保留历史的设计。我尽量将 UPDATE 的发生率降到最低。

数据可以很容易地——完整地——与“当前”记录和所有以前版本的记录一起保存。现在磁盘很便宜。用户更新的历史日志似乎效果更好。撤销用户更改很简单，因为以前的版本都可用。

我不再在“样板列”中看到任何值

---

how does storing the "table row ID" in the log help if the actual content(in other columns) are NOT saved ?

什么？行的先前值可以保存。这才是重点。您可以使用多种方法来保留历史记录。

1. 将历史表与以前的值分开。

2. “标志”——创建一个由两部分组成的 key ——具有“当前”与“历史”设置。

3. 可能使用一对“事件日期”和“非事件日期”。

还有其他技术。阅读有关渐变维度 (SCD) 算法的信息。

这些技术中的每一种都有独特的要求；它们是设计模式，而不是样板。