Type I
This methodology overwrites old with new data, and therefore does not track historical data. Its common uses are for misspelled names. (Assuming you won't need to know how it was misspelled in the past.)
Example of a supplier table:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State |
|---|---|---|---|
| 123 | ABC | Acme Supply Co | CA |
In the above example, Supplier_Code is the natural key and Supplier_Key is a surrogate key. Technically, the surrogate key is not necessary, since the table will be unique by the natural key (Supplier_Code). However, to optimize performance on joins use integer rather than character.
If the supplier relocates the headquarters to Illinois. The updated table would overwrite this record:
| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State |
|---|---|---|---|
| 123 | ABC | Acme Supply Co | IL |
The disadvantage of this is that there is no history in the data warehouse. Advantages of this type is it's easy to maintain.
If you have calculated an aggregate table summarizing facts by state, it will need to be recalculated when the Supplier_State is changed.
Read more about this topic: Slowly Changing Dimension
Famous quotes containing the word type:
“How is freedom measured, in individuals as in nations? By the resistance which has to be overcome, by the effort it costs to stay aloft. One would have to seek the highest type of free man where the greatest resistance is constantly being overcome: five steps from tyranny, near the threshold of the danger of servitude.”
—Friedrich Nietzsche (1844–1900)
“He turned out to belong to the type of publisher who dreams of becoming a male muse to his author, and our brief conjunction ended abruptly upon his suggesting I replace chess by music and make Luzhin a demented violinist.”
—Vladimir Nabokov (1899–1977)