modeling webinar: normalization - it's not your friend... or your enemy
TRANSCRIPT
![Page 1: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/1.jpg)
Karen Lopez @datachick #HeartData
Heart of Data ModelingNormalization…is not your friend…or your Enemy
![Page 2: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/2.jpg)
Yes, Please do Tweet/Share today’s event
@datachick #heartdata
![Page 3: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/3.jpg)
About Kerry
Recovering Sysadmin
Ten years of data—Varying degrees of SQL Server Engine & BI
Currently Senior BIDeveloper/Consultant
Used to fly little airplanes
@airbornegeek
![Page 4: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/4.jpg)
Karen López
Karen has 20+ years of data and information architecture experience on large, multi-project programs.
She is a frequent speaker on data modeling, data-driven methodologies and pattern data models.
She wants you to love your data.
![Page 5: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/5.jpg)
Data Modelers are people, too....so let’s get to know you….
![Page 6: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/6.jpg)
POLL: Who Are You?
![Page 7: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/7.jpg)
What Have You Worked on?
Aug 2014
![Page 8: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/8.jpg)
Attendees, be part of the webinar
Use Q&A for formal questions
Use chat to discuss with each
other
![Page 9: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/9.jpg)
Plan for Today
Why topic?
Why Normalize? Denormalize?
Myths and Truths
10 Tips for Talking About and Doing Normalization
![Page 10: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/10.jpg)
Confession time
I never normalize.
I don’t have the normal forms memorized, nor do I plan to
I believe understanding the principles is important
I believe normalization is NOT a process or a method. More later on that.Not everyone in the data world will agree with me on these things.
![Page 11: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/11.jpg)
NormalizationWhy this Topic?Why NOT this Topic?
![Page 12: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/12.jpg)
Normalization: It’s a Love/Hate Thing.
![Page 13: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/13.jpg)
![Page 14: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/14.jpg)
Normalization, very briefly…
Developed by Ted Codd in the 70s to address update anomalies.
One fact in one place, then referenced instead of duplicated.
One fact means one to create, one item to update, one item to delete.
![Page 15: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/15.jpg)
Normalization, briefly
![Page 16: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/16.jpg)
Normalization…
Is all about the keys, ‘bout the keys, ‘bout the keys…
Depends on understanding the MEANING of the keys and columns
Goes all to heck* when you have surrogate keys
Depends on the make up of the key parts (columns)
![Page 17: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/17.jpg)
Normalization, briefly
1NF – all instances (rows) have the same facts (columns). There are no repeating duplicate columns
2NF – only applies to multi-part keys. No fact is about just part of the key
3NF – No fact depends on another non-key column
![Page 18: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/18.jpg)
3NF
Every fact is either part of a key or depends upon the key, the whole key, and nothing but the key.….so help you Codd
Michael J Swart
![Page 19: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/19.jpg)
Remember…
“Normalization is like marriage…
…you always end up with more relations”
-Data Modeling Essentials, 3rd EditionSimsion & Witt
![Page 20: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/20.jpg)
Enterprise Applications are Complex
20
![Page 21: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/21.jpg)
Myths…
What Normalization is *NOT*
![Page 22: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/22.jpg)
Why People Hate Normalization
Performance
Misunderstood
Taught Wrong Not Sexy
![Page 23: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/23.jpg)
It’s taught wrong? Taught as a process, not a measurement
1NF, 2NF, 3NF, etc.
Used like a grade, instead of measurement
Magical “3NF”
![Page 24: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/24.jpg)
It’s not a process on real projects
1NF 2NF 3NF 4NF BCNF
![Page 25: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/25.jpg)
10 Tips for Data Modelers
1. Stop talking about the normal forms. Just stop.
2. Study the normal forms, know the lingo. Don’t use it, though
3. Be able to explain the anomaly you are trying to avoid.
4. Know your primary and alternate keys.
5. Enforce your primary and alternate keys.
![Page 26: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/26.jpg)
10 Tips for Data Modelers
6. Understand denormalization patterns.
7. Don’t take “needs to be Denormalized” as a change request.
8. Understand other performance tuning techniques and how to recommend them
9. Squash “JOINs are Evil” discussions. They are a feature.
10.Be flexible. That’s why we have architects & modelers.
![Page 27: Modeling Webinar: Normalization - It's Not Your Friend... or Your Enemy](https://reader030.vdocuments.us/reader030/viewer/2022032420/55a5e3891a28ab2d368b46c9/html5/thumbnails/27.jpg)
Thank you, you were great. Let’s do this next month!
Karen Lopez @datachick
#heartdata