0

Say I want to create a product dimension. It will have product id as primary key, product name as value.

The sales fact table will link to the product dimension on the product id key.

In the OLTP system the product table has normalized relationship with Product sub category table. And product sub category table has relationship to product category table.

I am trying to understand - On what basis or factors is a decision to be made regarding denormalizing tables and having the product category and sub category values into the product dimension table.

variable
  • 8,262
  • 9
  • 95
  • 215
  • I would say performance. Some argue that start schema is in principle better than snow flake, as it's more future proof, in case your model grows. But if your performance is good and your model will not grow in the future, then you might not need to remodel. – W.B. Feb 09 '21 at 19:42
  • https://stackoverflow.com/questions/44517192/data-warehousing-star-schema-vs-flat-table – RADO Feb 09 '21 at 23:28
  • I was thinking more like this: if the category had a numerical figure associated with it, for example- product1, category1, category1numericvalue. Now if we perform any analysis for product category then the numeric value will repeat. Where as if the numeric value was in the product category table, then each row of that table will have 1 record for the category and it's value. – variable Feb 10 '21 at 12:36
  • For example- calculating avg of the numeric value will work correctly when the category is separated out into separate table. Otherwise if the category name and numeric value is in product table then the avg will be based on number of products. – variable Feb 10 '21 at 12:51

0 Answers0