SCD 1维，没有代理密钥 [英] SCD 1 dimension without surrogate key

问题描述

This reference to Kimball group state that all dimensions should have surrogate keys except some very predictable one like date diemnsion.

我的情况与 SCD类型1 Wiki页面:

从技术上讲，不需要替代键，因为该行将由自然键(Supplier_Code)唯一.

Technically, the surrogate key is not necessary, since the row will be unique by the natural key (Supplier_Code).

数据是从操作系统中加载的，没有代理密钥，而我根据单个且唯一的 xxx_code 列在ETL中计算代理密钥.SCD类型1，满载.

Data are loaded from operational system without surrogate key, while I calculating surrogate key in ETL based on single and unique xxx_code column. SCD Type 1, full load.

在我的情况下，是否有任何理由使用代理密钥而不是通过 xxx_code 列创建自然密钥?

Are there any reasons to use surrogate key in my case instead of making natural key from xxx_code column?

推荐答案

代理键具有某些优点:

• 它们保护数据，因此内置密钥不需要在任何地方都可见.
• 它们允许更改代理密钥.
• 代理键通常是数字，比复合键或字符串更有效.

总而言之，自然键有时可能非常有用.例如，我使用相当复杂的系统链.在每个系统中引入新密钥刚开始变得令人困惑-例如产品代码.

All that said, natural keys might be quite useful sometimes. For example, I work with rather complex chains of systems. Introducing new keys in each system just starts to get confusing -- which is the product code, for instance.

尽管我偏爱代理键，但在这种情况下自然键可能会很有用.重要的是，这样的键应该是主键，它们实际上不能更改值，并且它们最好是数字(对于外键关系更有效).

Despite my bias toward surrogate keys, natural keys can be useful in such cases. What is important is that such keys be primary keys, that they really cannot change values, and that they are -- preferably -- numbers (which are more efficient for foreign key relationships).

这篇关于SCD 1维，没有代理密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！