Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For example we have:

Surrogate key: ABC123
Primary key: 1
Name: James

Is this legit in a Data Warehouse table?

share|improve this question
    
I assume you're talking about a dimension table, right? –  Jon Seigel May 10 '13 at 18:06
add comment

2 Answers

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.

share|improve this answer
add comment

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?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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