Designing dimensions
The first thing to look at is the primary key to use for a dimension table.
Defining the primary key of a dimension table
To get straight to the point: we always use surrogate keys for dimension tables. In Chapter 1, Introduction to Databases, we discussed logical versus surrogate keys. We will not repeat the discussion here. The best practice is to use surrogate keys for dimension tables.
In a star schema database model, using an efficient primary key is even more important than in a normalized OLTP database. In earlier examples, it became clear that fact tables might become really big in terms of the number of rows they store. Suppose you have a fact table with seven dimensions that has 1 billion rows. The difference between using keys that are 4 bytes in size and keys that are 8 bytes in size is 7 x 4 x 1,000,000,000, which is 28 GB. Some people might argue that today 28 GB is not really something to consider. But you might have a lot more rows than...