Here is the situation: There is a dimension with a natural key that is a good candidate for a primary key. However, there are a few columns that can have (and does have - 10% of the time) multiple values. The majority of the data have only one value per key.

My options:

  1. For the 90%, put it into the table and have "multiple" as the value for the rest; and snowflake the list.
  2. Same as #1, but LISTAGG the values. This will make numeric columns - non-numeric.
  3. Create a surrogate key, and replicate the rest of the columns with a flag to pick one of the rows - to avoid coding DISTINCT is the attribute is not being used.
  4. Just snowflake the column to another table.
  5. Any other ideas?
  • Presumably by "snowflake a column" you mean remove a list-valued column from a table and add a new table with columns for some key of the original table, a column for list elements and a row for every unique pair of a key value and list element value? – philipxy May 17 '16 at 4:06

Your Answer

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

Browse other questions tagged or ask your own question.