I am learning about database warehouses and data modeling. I came across this blog post. In the section, "DIFFERENT MODELS HAVE DIFFERENT PURPOSES" the author mentions that depending upon the need of the business, there can be two types of data modeling:
- Relational
- Dimensional
However, in both examples the author says that the modeling is done in 3NF form, I am confused, how is 3NF and star schema both used in dimensional model. Aren't they supposed to be fundamentally different?
Lastly, if I understand correct, the relational data modeling is done for OLTP systems and Dimensional is done for OLAP systems, right?
Best Answer
The post you mention does not say "modeling is done in 3NF form" in both relational and dimensional models. It only states that 1) star schemas are common in dimensional models and 2) 3NF schemas may also be used. It's an over-simplified statement but keep in mind the post is titled "Data Modeling Explained in 10 Minutes or Less" :).
In a nutshell: the biggest difference between dimensional, tabular and relational modeling is the measure of duplicate data being stored. Dimensional and tabular models contain (much) more duplicate data (de-normalized), but that allows them to be able to quickly 'slice and dice' it. Relational models do their best to store data only once in the model (normalized), allowing for fast transactional processing.
One does not exclude the other. It all comes down to which model provides the best performance and widest range of possibilities. And that, in turn, boils down to using the right tool for the right job.
Which leads me to your last question: relational models are commonly found in OLTP systems and dimensional (or tabular) models are commonly found in OLAP systems.