When you use a bridge table, isn't that a snowflake? I thought this was not allowed in dimensional modeling.The use of a bridge is a limited form of snowflaking. It is also an important feature of the dimensional model.
Bridges are widely accepted, even among those who would otherwise avoid snowflake designs.
A Limited form of Snowflaking
In a Q&A from last year, I suggested that a snowflake occurs when dimensions are joined to something other than a fact table. By this definition, the examples from the previous two posts are certainly snowflakes.
In the first example, a bridge was used to associate a dimension with a repeating attribute (a company with multiple industries.)In the second example, a bridge was used to associate a fact with multiple dimension rows (a sale with multiple salespeople.)
In both cases, a standard one-to-many relationship between fact and dimension does not exist.1 The bridge table solves this problem.
This usage is widely accepted. In fact, the bridge table is a central feature of dimensional modeling. I devote two full chapters to bridged designs in Star Schema: The Complete Reference
. Other Relationships are Not Modeled
When a bridge table is employed, most dimensional modelers will still refer to the schema as "a star." That's because the bridge is only used in the situations described above -- when the standard master-detail relationship between dimension table and fact table breaks down.
Other kinds of relationships between data elements are not resolved via multiple tables. In our examples:
- Repeating attributes are permitted (work phone, home phone, mobile phone)
- Dimensions contain partially dependent attributes (brand information present in a product table)
- Master-detail relationships are not instantiated into separate tables
- Values associated with codes are not placed in look-up tables
- Dimensions are not linked to one another
Other Times to Snowflake?
There may be some other situations where limited snowflaking is acceptable. One is where a set of attributes (e.g. geographic) would otherwise appear in multiple dimension tables. Another is where software tools (the RDBMS or BI software) work better in a snowflake environment.
These uses are a bit more controversial, and I wrote about them in a previous post.
When it comes to the bridge table, however, there is no controversy. The bridge is an accepted technique in dimensional modeling, and is often necessary for specific dimensional designs.
- Chris
1 Bridges may also be used to navigate recursive relationships, as I describe in the book.
Image credit: Gravityx9 licensed under Creative Commons 2.0
