Tuesday, October 27, 2009

Slowly Changing Dimension: Char for Type II Attributes

I used to use Char data type for character data types of variable length columns, if the maximum number of characters that will set for the attribute is less than 10. This is what most DBAs/DBEs do. Not only the maximum length, there are few more considerations for using Char instead of Varchar for character data types of variable length. One would be, when the length of all data values is approximately same. I made a mistake; this cannot be applied in everywhere, especially in Relational DW with Type II attributes. For example, if I have to make a dimension table with Marital Status that contains either Single or Married, I can make the column as either varchar(8) or char(8), so I made it as char(8) because I prefer to make my design according to experts suggestions. Unfortunately this is a Type II attribute which is designed to maintain history, hence when the record contains “Single”, even though the same value comes with a new data set, it considers as a changed (because of the length) and inserts a new record, making the old record as a historical record and it continues with next data set... It unnecessary adds new records to the table making the old ones as historical records. So, I made the all the Type II columns as varchar columns that were set as char to solve the issue, what do you think, any suggestions, any thoughts on this?

No comments: