Standardize Field Values – Use Translation Table or A Existing-VS-Standard-value Table W/ More Flexibility
I was initially using a very simple translation table initially to translate existing un-standardized values to standard values. This turns out to be too restrictive, and offers very little flexibility. Here is a typical translation table. I can only rely on two fields, SourceSystem and the FromValue to derive the ToValue. This works fine the standard value depends only on one existing value. When the relationship gets a little more complex than this, the translation table gets in the way.
A more flexible design is to have separate table for each set of standardized values. Here is an example.
The standard PC brand and model depends on the existing PC brand and model, and also the existing item type. An extra Exclude field can be also added to the table, so the standard values table can be also served as a filter for data that should be excluded from the ETL process.
