I’m reading the 2nd Edition of Chris Date’s An Introduction to Database Systems (1977) as referred to by Dr. Ralph Kimball in https://www.kimballgroup.com/1997/08/a-dimensional-modeling-manifesto/. What I find interesting is dimensional modeling is not denormalized where it matters, in the fact tables, which are in third normal form. It’s only the dimension tables that are normalized for performance and understandability reasons. It’s really more of a reorganization than a normalization. Instead of relating general entities in 1 to many relations, we are relating dimensions through the fact tables.
hey I like the normalisation article - a couple of thoughts.
1. Whatever you decide to do find a way to document your decision so that someone coming after you can understand WHY you did this .... your product_sales table may have 7day_sales, 14day_sales, 28day_sales columns in it to make weekly, fortnightly, monthly summaries easier - it was a physical design decision you made - if the assumptions or dependencies change (like no more monthly sales reports) then the design has to change
2. The beauty of separating conceptual, logical and physical data models is that they can exist at different levels of normalisation. The physical design may be quite denormalised (oh no not another OBT design!), yet it probably reflects a more normalised set of concepts and logical model. I tend to think a conceptual model should be fully normalised and the logical model mostly normalised, whereas the physical model can be quite denormalised. This is where I think data teams also have problems - they often cannot operate with different models.
I’m reading the 2nd Edition of Chris Date’s An Introduction to Database Systems (1977) as referred to by Dr. Ralph Kimball in https://www.kimballgroup.com/1997/08/a-dimensional-modeling-manifesto/. What I find interesting is dimensional modeling is not denormalized where it matters, in the fact tables, which are in third normal form. It’s only the dimension tables that are normalized for performance and understandability reasons. It’s really more of a reorganization than a normalization. Instead of relating general entities in 1 to many relations, we are relating dimensions through the fact tables.
You have written very well! Are there any upcoming posts by SeattleDataGuy about Kimball dimensional modeling or Data Vault?
hey I like the normalisation article - a couple of thoughts.
1. Whatever you decide to do find a way to document your decision so that someone coming after you can understand WHY you did this .... your product_sales table may have 7day_sales, 14day_sales, 28day_sales columns in it to make weekly, fortnightly, monthly summaries easier - it was a physical design decision you made - if the assumptions or dependencies change (like no more monthly sales reports) then the design has to change
2. The beauty of separating conceptual, logical and physical data models is that they can exist at different levels of normalisation. The physical design may be quite denormalised (oh no not another OBT design!), yet it probably reflects a more normalised set of concepts and logical model. I tend to think a conceptual model should be fully normalised and the logical model mostly normalised, whereas the physical model can be quite denormalised. This is where I think data teams also have problems - they often cannot operate with different models.