Database Design: Several Potential Identifiers

Question

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

For example: 70% have ISIN , some of them have bloomberg code, some of them both, some of them have only AccountingID , coming from the local accounting package, etc.

In general, I end up providing this table with a surrogate PK, plus various fields for all possible identifiers ( Bloomberg, ISIN, AccoutingID , ..)

I once inherited a database where the developer transferred alternative keys to the [Identifiers] child table, on the assumption that he did not know in advance all possible alternative keys.

This identifier table looked like this:

• AssetID (surrogate)
• IdentifierType (ex: ISIN)
• IdValue

What is the best solution?

I think the first (single table) is best, because even if I risk having multiple Nulls, ISIN is ISIN and is the correct attribute of Fund .

Answer 1

I would make a separate table because the Identifiers table method makes assumptions about the idValue data type. What if you get something new that uses guid rather than int?

You can still make a separate column for each possible asset identifier and save the asset data in a separate table that separates the surrogate identifier. The approach you take will depend largely on how you use the data and how often you can add new types of asset identifiers.

Answer 2

It depends a little on your needs, but the second approach is often more flexible, because you can provide a reasonable interface for inserting a new "Identifier" record that does not require changing the database schema.

This is usually used if you do not know how many identifiers can exist, or if you knew that you needed to add more time.

The first approach is simpler in terms of writing queries and, if the identifiers are static, perhaps the easiest to use.