Congratulations for this discovery. I am surprised this is not tought in uni and bootcamps - after all this is not new insight, it has been known for at least 3 decades now.
OLAP and the "need" for denormalisation is a misconception. OLTP/OLAP refer to the physical aspect of SQL database products but underlying it we suppose the relational data model and this is the same independently of OLTP/OLAP. An "analytical table" should also be normalised (5N or 6N not 3N [or more commonly Boyce-Codd Normal Form]). The "denormalisation" might appear as an intermediate logical step (not part of the data model per se) before an aggregation (or other function) is calculated but once data is aggregated the result relation should be normalised too.
Thank you Mark, I agree that the OLTP vs OLAP divide is not talked about enough in the data world. However, I thought your database example was a bit contrived. There is no reason for the User Product Reviews table in 3NF, you can just have the product_id and user_id column in the Reviews table because each review has exactly one product_id and user_id.
Congratulations for this discovery. I am surprised this is not tought in uni and bootcamps - after all this is not new insight, it has been known for at least 3 decades now.
OLAP and the "need" for denormalisation is a misconception. OLTP/OLAP refer to the physical aspect of SQL database products but underlying it we suppose the relational data model and this is the same independently of OLTP/OLAP. An "analytical table" should also be normalised (5N or 6N not 3N [or more commonly Boyce-Codd Normal Form]). The "denormalisation" might appear as an intermediate logical step (not part of the data model per se) before an aggregation (or other function) is calculated but once data is aggregated the result relation should be normalised too.
Thank you Mark, I agree that the OLTP vs OLAP divide is not talked about enough in the data world. However, I thought your database example was a bit contrived. There is no reason for the User Product Reviews table in 3NF, you can just have the product_id and user_id column in the Reviews table because each review has exactly one product_id and user_id.