For example we have:

Surrogate key: ABC123
Primary key: 1
Name: James

Is this legit in a Data Warehouse table?

  • 1
    I assume you're talking about a dimension table, right? – Jon Seigel May 10 '13 at 18:06

A table can and should have as many keys as it needs. Usually when a surrogate key is used it means you will also want some alternative key as well (variously called a domain key, natural key or business key).

The practice of designating any one key as "primary" is of no great significance. It is simply a convention to mark one of the keys as "preferred" or important in some way that has meaning for the database user. Typically the primary key is the one referenced by foreign key constraints in other tables.

| improve this answer | |

It depends on the purpose of the key. "Surrogate key" can mean different things to different people; to my mind, it means a candidate key (a field or combination of fields which uniquely identify a record) other than the primary key. For example, social security number may be a surrogate key for customers (not a very good one, mind).

If the values are alphanumeric as in your example, it's probably a business key. If warehouse users are likely to need this key, then sure, include it. On a fact table, if there are several such keys, and/or they're unusually wide, consider flaking them out to keep your fact table narrow.

If it's a synthetic key, a key created for use in the warehouse to avoid the many problems associated with business keys, then sure, include it. Indeed, this may well be your primary/clustering key.

I'm not sure this answers your question. Can you be a little more specific? Are you considering whether to include this field in a warehouse you're designing?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.