非规范化数据或多列键？
我正在尝试在实现小型 SQL Server '08 数据库时做出判断。
我正在将平面文件数据库的输出文本文件从旧的 COBOL 系统转换为前面提到的 SQL Server 数据库。它是一个车辆和房地产贷款的数据库，可以通过贷款人 ID（七位数字）、银行帐号（15 位数字）和“帐户后缀”（两位数字）的组合来唯一标识。
我承认我在数据库管理方面非常天真（说实话，直到我目前的职位为止我还没有真正做到这一点），并且我正在尝试确定两种方法中哪一种是我实现数据库管理的最佳选择键将索引到其他几个表中：
1）使用上述值的三列键来识别每笔贷款，或者
2) 通过实现“键”列来对数据进行非规范化，该列是组合三个值的 24 个字符的字符串。
当然，非规范化是丑陋的，但我无法预料会发生更新异常，因为贷款不能在银行之间来回传递或更改其贷款后缀。这些值的更改保证是不同的帐户。
复合键更优雅，但我读过一些论文表明这是一件坏事。
那么，哪个选项可能是更好的选择，更重要的是，为什么？
I'm trying to make a judgment call in implementing a small-ish SQL Server '08 database.
I'm translating an output text file of a flat-file database from an old COBOL system to the aforementioned SQL Server database. It's a database of vehicle and real estate loans, which can be uniquely identified by the combination of a Lender ID (a seven-digit number), bank account number (15 digits), and "account suffix" (two digits).
I confess I'm pretty naive when it comes to database administration (to be honest, I've not really done it up until my current position), and I'm trying to determine which of two approaches are my best option for implementing a key which will index into several other tables:
1) Identify each loan using a three-column key of the above values, or
2) Denormalize the data by implementing a "key" column which is a 24-character string combining the three values.
The denormalization is ugly, granted, but I can't anticipate update anomalies occurring, since loans can't be passed back and forth between banks or change their loan suffix. A change in those values is guaranteed to be a different account.
A compound key is more elegant, but I've read a few treatises suggesting that it's a Bad Thing.
So, which option is likely to be a better choice, and more importantly, why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我将使用自动生成的代理键，然后在自然键上放置唯一索引。这样，如果自然密钥发生变化（并且可能会说一家银行被另一家银行收购），那么它只需要在一个地方进行更改。使用代理键最重要的是确保自然键的唯一性（如果存在）并且唯一索引可以做到这一点。
I would use an autogenerated surrogate key and then put a unique index on the natural key. This way if the natural key changes (and it might if say a a bank got bought out by another bank), then it only needs to change in one place. The most importatnt thing in using a surrogate key is to ensure uniqueness of the natural key if one exiusts and the unique index will do that.
如果这是不会经常更新的参考数据，那么使用多部分密钥应该没问题。
如果这是高流量事务数据，则添加代理键（int Identity、聚集主键）并使三部分键成为备用键。
我根本不建议实施选项 2。
If this is reference data that won't be updated often, then using the multi-part key should be fine.
If this is high-traffic transactional data, then add a surrogate key (int identity, clustered primary key) and make the three-part key an alternate key.
I would not suggest implementing option 2 at all.
我建议只使用自动递增的数字代理键。为什么它需要是其他三个“关键”列的混搭？
I would suggest just using an auto-incrementing numeric surrogate key. Why would it need to be a mashup of the other three "key" columns?