1

Trying to create a simple lookup table for a drop down on a BI web page. The table contains 2 columns, an key and a description.
What is the best practice with regard to Dimensional Modeling when designing this table? Would key => number, description => character (column => data type) be sufficient or does the key need to be a SID?

Think of a dropdown in a web page or web form like "Country".

  • 1
    Do you mean a reference table, such as the ones used for Master Data Management? – World Wide DBA Feb 13 at 22:54
  • This is just a lookup table, with an id (key) and description. It is static and the description will populate a dropdown in a web form. This is not related to MDM specifically although such a table could exist in a MDM environment. – tale852150 Feb 13 at 22:58
  • 1
    How is that description then used once it has populated the drop down list? Will the value eventually make its way into the dimensional model, for example as an attribute on a dimension? – World Wide DBA Feb 13 at 22:59
  • 'yes' it will eventually make its way into the dimensional model into a dimension table. – tale852150 Feb 13 at 23:17
1

If your natural key can only ever be associated with one description, and your lookup table only contains static reference data, then I see no need for a surrogate key.

In the example you gave, your drop down list would contain countries. From my perspective, if the dimensional model is based upon the star-schema then country would not become an independent dimension, but would instead be an attribute on another dimension (such as geography or customer etc). This would not require the surrogate key to be included even if you were to add one and therefore would be a waste of time, effort and storage.

If your dimensional model is based upon the snowflake or your key can be associated with more than one description (for example if the description can change over time) then yes I would include a surrogate key. I would not put this on the reference table though, the surrogate key should belong to the dimension that owns the data.

| 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.