#74: How Would You Model This Data? (Example)

Here is the scenario I put in front of a group of data people. A transaction fact table joins to a product dimension, and the source system also has a separate table of product types. Should product type become its own dimension, or a column on the product dimension? My recommendation is the column: one wider dim_products, one join from the fact table, less to maintain. Split it into a separate dim_product_types only when the type carries real context of its own, such as many attributes or history you want to track on the type itself. What I would not do is chain dim_products to dim_product_types, because that is application database thinking applied to an analytics model.

The scenario

Data modeling stays abstract for a lot of people, and worked examples help more than rules do. So I posted a small set of tables and asked how others would model them as facts and dimensions.

The setup is ordinary. There is a main transaction fact table that references product information through a product key, and a product dimension that holds the descriptions.

That part is not controversial. The nuance is the third table.

The source system also keeps a table of product types, and every product has a type. So the source looks roughly like this:

transactions            products                product_types
  transaction_id          product_id              product_type_id
  product_id              product_name            product_type_name
  quantity                product_type_id
  amount

The question is what to do with that third table. Give product type its own dimension, or merge it into the product dimension and keep one table?

Why the answers varied

There are textbook answers to a lot of modeling questions, and this is close to one of them. Even so, the replies did not line up.

That is usually a good sign rather than a bad one. People bring their own context: the size of the team, the reporting they already support, how much the source system churns.

The two options I gave

There are two reasonable answers here, and I lean hard toward the second one.

Option 1: a separate product type dimension

You can build dim_product_types and relate it to the fact table directly. The fact table then carries two identifiers instead of one.

fct_transactions                dim_products
  transaction_id                  product_id
  product_id        ---------->   product_name
  product_type_id   ------+       product_sku
  quantity                |
  amount                  |     dim_product_types
                          +---->  product_type_id
                                  product_type_name
                                  product_type_category

This works. It makes the most sense when there is significantly more context about the type than a single label.

Maybe the type has a dozen columns of its own. Maybe those values change slowly over time and you want to track that history as its own thing. In that case a separate slowly changing dimension earns its place.

Option 2: product type as columns on dim_products

The option I would recommend is one product table. Keep dim_products, add the type attributes as columns on it, and the fact table has a single product relationship.

fct_transactions                dim_products
  transaction_id                  product_id
  product_id        ---------->   product_name
  quantity                        product_sku
  amount                          product_type_id
                                  product_type_name
                                  product_type_category

The table gets a little wider. You may add a few type columns that repeat across products of the same type, and that is fine.

Which one I recommend

In exchange for that width you simplify the model. One connection to the product dimension instead of two, and no second table to keep in sync.

You also keep your history options open. If the type changes, you can still treat it as a slowly changing dimension, the change just lands on dim_products instead of on a separate table.

The cost of the first option is quieter than it looks. A second identifier on the fact table means a second grain question to answer, a second key to populate during loads, and a second place for a mismatch to hide.

Unless the type really is a rich entity of its own, the simpler model wins.

Why I would not snowflake it

One response was worth sharing because it points at a bigger idea. The suggestion was a snowflake schema: have dim_products join directly to dim_product_types rather than routing through the fact table.

The argument was that this is what the data itself says, so why not model it that way.

What that looks like

snowflake:  fct_transactions --> dim_products --> dim_product_types

star:       fct_transactions --> dim_products
                             --> dim_customers
                             --> dim_date

Why the star keeps dimensions one hop away

Like most things in data, it is not forbidden. You can do it, and there are modeling approaches where that normalized shape is the point.

But if you have chosen the facts and dimensions path, I think it matters to stay consistent with the concept of a star. A true star attaches facts directly to dimensions, not dimensions to other dimensions.

There are always exceptions. In practice, chaining dimensions is where many to many relationships creep in, queries fan out, and you end up with duplicate rows that nobody can explain.

Application databases and analytics models are different

That snowflake suggestion is really a database design instinct showing up in the wrong place. It is worth separating the two.

How application databases are designed

An application database exists to make the application work. You get separate, isolated tables: a products table, a product types table, each one unique and normalized.

That shape is right because applications mostly read row by row. Look up a single user, stitch together the handful of rows that describe them, return them to the screen.

Take a website backed by a database. To show everything about one user, the application pulls a few rows keyed on a user id and assembles them.

There is no heavy aggregation involved, just individual pieces of information retrieved quickly. For that access pattern, a normalized row based model is the efficient choice.

How analytics models are designed

Analytics does the opposite. You are aggregating, summarizing and scanning much larger volumes, so the design looks different.

That is where you see wider tables and flatter models. It is also true of most modern cloud warehouses, which are columnar rather than row based, so the cost math is simply not the same.

Your job is to consolidate, not to copy

Modeling for analytics also hands you more control. You get to design around business needs rather than around what the application happened to require.

Flat, purpose built tables are easier for end users and reporting tools to work with than a reassembled copy of the source system.

That is the role: pull data together, sometimes from several sources, and build an internal structure meant for reporting. Not for an application, and not for a vendor product.

So when a modeling question like the product type one comes up, this is the conversation happening underneath it. Answer that first and the table layout tends to follow.

Related reading

• Comparing 3 Types of Data Modeling
• The 4 Types of Dimensions
• The 3 Types of Facts
• The Struggle of Data Modeling

Final takeaway

Most modeling debates I get pulled into with small teams are not really about the tables on the screen. They are about whether the model is being designed for an application or for analytics, and once that is settled the table choice usually makes itself.