This post is Day 8 of the 12 Days of SQL, a series that was the idea of Brent Ozar (blog | Twitter) to have members of an elite group of foodie friends bloggers and writers who get together in person and virtually to improve our writing, branding, blogging and business skills. This series has us picking our favourite SQL Community blog entry from outside the group and sharing with our readers.
On Day 7, Yanni Robel (blog|Twitter), DBA extraordinaire and superwoman at allrecipes.com, wrote about Jonathan Kehayias’ post on No such thing as Small Change to Production database and the Change Management Process. I first met Yanni during SQLCruise. She also took us to a Disney Character Breakfast at Disneyland last week. This is significant because being kid-free, we don’t get out much when it comes to kid stuff. We had a blast – a future blog post to follow.
For Day 8 I suppose that I should write a post that has to do with “Eight Maids a Milking”…but that’s just a bit too weird for a part-time vegan hippie woman in IT to pull off. So I’m going to go with a football theme. That’s my George Bailey in a football uniform ornament on the left. Let’s call this day’s present “Eight Tight Ends”. Or “Eight Ends a Tightening?” Either one works.
I have chosen Louis Davidson’s Can you over-normalize? post. Louis is a frequent speaker, book author, and blogger who focuses primarily on database design issues. I met him in person recently at SQLPASS and volunteer with him on the PASS Data Architecture Virtual Chapater (DArcVC).
In his post, Louis asks an important question, “Can you over normalize?” His answer is that no, you really can’t. But before you get your tinsel in a tangle, read what he said:
My rule of thumb is that:
1. The requirements dictate the database design
2. The requirements and the relational engine dictate the implementation
So unless you understand the requirements, you can’t design the optimal database, and if you don’t understand SQL Server, you are not going to end up with an optimal implementation. Over-normalize? No. Over-engineer? Definitely.
You know that I love contentious issues and levels of normalization is one of the major ones. What I find most people miss in discussions about normalization is that it’s relevant to inserts, updates and deletes. We normalize to avoid data anomalies that would decrease data integrity. But when people complain about “over normalization”, they are usually looking only at retrieving data, not maintaining it. All normalization looks excessive when you aren’t concerned with data integrity. This leads to people wanting to trade off data integrity for performance gains, which is something we might do based on costs, benefits, and risks for the entire lifecycle of data. That’s why we denormalize data. Sometimes the trade off is acceptable and sometimes it isn’t.
If you read them comments on Louis’ post, you’ll see just how many people still don’t understand the basics of normalization – why it is done, why it might be undone, why there isn’t a single number for the “right” level of normalization…which in a way was his point.
As you may have heard me say in a presentation:
There is no one right answer for all projects, all designs, all organizations, all environments.
This is why sports books have an Over/Under for a specific game. They can’t have a single Over/Under for all teams, all locations, all days, all weather conditions. There is no one right number. Your normalization number should be based a on a whole sleigh ride full of inputs, including performance, data quality levels, trust, data architecture, etc. One project’s normalization design choices might be over done and a similar approach for another project might be undernormalized.
In fact, even George Bailey was calculating his Over / Under for Mary Hatch as she hid naked in the hydrangea bush. That football uniform was serving him well in his calculations.
Take a couple of minutes and Buffalo Gals your way over to Louis’s post and let me know what you think.
Next up is Kendra Little (blog|Twitter) who is going to write about Nine Ladies Dancing for Day 9, I hope. Kendra is an amazing presenter and DBA who works in the Pacific Northwest. I first met her at SQL Saturday Miami. If you ever get an opportunity to attend one of her presentations or webinars, Charleston right to it. She has fabulous slides and she shares a ton of practical information about SQL Server.
Brent O’s 12 Days of SQL post
Day1: Jeremiah Peschka
Day 2: Grant Fritchey
Day 3: Dave Stein
Day 4: Andy Leonard
Day 5: Erin Stellato
Day 6: Tim Ford
Day 7: Yanni Robel
Day 8: [This post] Karen Lopez
Day 9: Kendra Little
Day 10: Crys Manson