审计表与类型2慢变化维度：区别和用途

Question

在SQL Server 2008+中，我们希望启用对操作数据库中“Customers”表历史更改的跟踪。这是一个新表，我们的应用程序控制所有对数据库的写入，因此我们不需要像触发器这样的恶意黑客。相反，我们将把更改跟踪构建到我们的业务对象层中，但我们需要找出正确的数据库架构来使用。

行数将少于100,000行，每个记录的更改次数平均为1.5次/年。

至少有两种建模方法我们一直在考虑：

1. 创建一个名为CustomersHistory的类型2缓慢变化维度表，其中包含EffectiveStartDate、EffectiveEndDate（对于当前版本的客户设置为NULL）和审计列，如ChangeReason和ChangedByUsername。然后，我们将在该表上构建一个名为Customers的视图，该视图被过滤为EffectiveEndDate=NULL。大多数应用程序部分将使用该视图进行查询，只有需要了解历史记录的部分才会查询底层表。为了提高性能，我们可以实现该视图并/或者在EffectiveEndDate=NULL上添加过滤索引。

2. 使用单独的审计表。每次更改Customer记录都会写入Customer表和CustomerHistory审计表中。

从快速查看StackOverflow问题来看，#2似乎更受欢迎。但是这是因为大多数数据库应用程序必须处理遗留和流氓编写者吗？

考虑到我们从零开始，采用任何方法都有其优缺点。你会推荐哪种方法？

- Justin Grant

---

这是一个 OLTP 数据库，而不是一个独立的数据仓库，但是涉及到的表并不经常更改。 - Justin Grant

我想象中应用程序中常见的操作是显示给定客户的交易列表。每次都需要进行额外的连接操作 - CustomersCurrentView WHERE Customer = 'John Doe' JOIN CustomersHistory JOIN Transactions。我的建议是 - 如果历史数据很少被使用，将其保留在单独的审计表集合中；只有当历史感知组件构成应用程序的重要部分时，才考虑SCD 2变换。非常有趣的问题！ - Marek Grzenkowicz

Answer 1

通常，SCD Type-II 的问题在于，如果属性值的平均更改次数非常高，您最终会得到一个非常庞大的维度表。这个不断增长的维度表与一个巨大的事实表连接，会逐渐降低查询性能。就像缓慢的毒药一样……起初你看不到影响，等你意识到时，已经为时已晚！

现在，我明白你将创建一个单独的物化视图，其 EffectiveEndDate = NULL，并且该物化视图将在大多数连接中使用。此外，对于您而言，数据量相对较小（100,000）。由于每年平均更改仅为1.5次，因此我认为数据量 / 查询性能等在不久的将来都不会成为您的问题。

换句话说，您的表确实是一个慢变维度（与快速变化的维度相对），在这种情况下，我会更喜欢选项#1。

- hashbrown