数据库设计：多个潜在标识符 [英] Database design: multiple potential identifiers

问题描述

当创建资金或资产表时，我经常遇到同样的问题：不是全部资产具有相同的标识符。

When creating a Funds or Assets table, I am often confronted with the same problem: not all Assets have the same identifier.

例如：70％有一个 ISIN ，有些有一个bloomberg代码，有些有两个，一些只有一个 AccountingID 来自本地的会计包，等等。

Eg: 70% have an ISIN, some have a bloomberg code, some have both, some only have an AccountingID coming from the local accounting package, and so on.

一般来说，我最后给该表一个代理PK，加上所有可能的标识符的不同字段（ Bloomberg，ISIN，AccoutingID ，..）

Generally I end up by giving that table a surrogate PK, plus distinct fields for all the possible identifiers (Bloomberg, ISIN, AccoutingID,..)

我曾经继承了这样一个数据库，开发人员已经将备用键迁移到子表[Identifiers]，基于事实上他并不知道每个可能的替代键。

I once inherited such a database where the developer had migrated the alternate keys to a child table [Identifiers], based on the fact that he did not know in advance every possible alternate key.

此标识符表格如下所示：

This Identifiers table looked like this:

• AssetID （代理一个）


• IdentifierType （例如：ISIN）


• IdValue

• AssetID (the surrogate one)
• IdentifierType (e.g: ISIN)
• IdValue

最好的解决方案？

我认为第一个（单表）是最好的，因为即使我有几个空值，ISIN是一个ISIN，是

I think the first (single table) is best because, even if I risk having a few Nulls, an ISIN is an ISIN and is well defined attribute of the Fund.

推荐答案

我会做单表因为Identifiers表方法对idValue的数据类型做出假设。如果你得到一些使用guid而不是int的新东西怎么办？

I would do single table because the Identifiers table method makes assumptions about the datatype of the idValue. What if you get something new that uses a guid rather than an int?

您仍然可以为每个可能的资产ID执行单独的列，并将关于资产的数据保存在一个单独的表中，从而替代代理ID。您采取的方法主要取决于您将如何使用数据，以及添加新资产ID类型的频率。

You could still do a separate column for each possible asset ID and keep the data about the assets in a separate table that keys off the surrogate ID. The approach you take will depend mainly on how you will use the data, and how often you might add new asset ID types.

这篇关于数据库设计：多个潜在标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！