Database Reference
In-Depth Information
Figure 4-5.
A typical dimension table
We realize that some may not agree with this decision, so let's examine this idea. It is true that performance
might be enhanced using a smaller datatype, but if it can be determined that the Titles table will have only a few
hundred rows, there will be little to no measurable decrease in performance. If, however, there are millions of
titles, then of course you will want to change to the smaller datatype to increase performance.
Design your tables around the philosophy that simplicity is more important than pure efficiency and
performance is more important than total simplicity. That is to say, if you can make it simple and not adversely
affect performance, keep it simple! If, on the other hand, simplifying things decreases your performance to a
noticeable degree, ignore your simplification efforts for this occurrence. Use common sense, and evaluate your
needs on a case-by-case basis. It is possible to get too caught up in defending one style over another when often
there is little impact either way.
Stars and Snowflakes
At one end of a lunch table you may hear the simplicity versus performance argument, and at the other the
stars versus snowflake argument. Ignoring the fact that you need to find a more exciting place to eat lunch, let's
examine the difference.
Star and snowflake designs reference a pattern form between dimension tables when compared to a fact
table. A better name for them, however, would have been single-table dimensions and multitable dimensions; let
us explain.
In Figure
4-6
, you see a star design that forms a ring of dimension tables around a centralized fact table. In
the star design there is only a single circle of dimension tables around the fact table of a data mart. Whether there
are three, four or a hundred dimensions in the data mart, it makes no difference; as long as there is only a single
table for each of these dimensions, this pattern still forms what is known as a star design.
Dimensions containing multiple tables per dimension are snowflake designs. Snowflakes form a circle of
two or more tiers of dimensional tables around the fact table of a data mart. Whether it forms a circle of three,
four or hundreds of tiers, it still is a snowflake design. Figure
4-6
outlines the pattern of a snowflake design
compared to a star design.
If you are thinking, “Really? Is that all there is to it?” you are not alone. Randal has had many a student enroll
in his classes specifically to learn the difference between a star and a snowflake design. How anticlimactic to
discover the answer is so simple!
