Q&A with Louis Davidson and Paul Nielsen
Stand Up for Excellent Database Design
In the SQL Server community, two MVPs consistently speak up for excellence in schema design. Louis “DrSQL” Davidson and Paul Nielsen of SQL Server Bible fame are serious about the importance of a good data model, which reduces the overall aggregate workload of the database and is the key to performance, data integrity, extensibility, and usability.
But don’t expect a lecture—or sermon—during their Nov. 6 PASS Summit Unite 2009 post-conference seminar, “Advanced Relational Database Design.” This dynamic duo is more like a stand-up comedy team as they banter through the gritty details of normalization and database design. PASS tracked down these SQL Server MVPs for a quick preview.
Q: What challenges do SQL Server pros face in doing excellent database design?
Paul: Database design today faces three interrelated threats. One, management seems to have less respect for database design. The thought seems to be “Get it done fast—with today’s hardware, we don’t need perfect.” Two, more and more databases are being developed by application developers who don’t understand data modeling or the need for data modeling. And three, data modeling is starting to be perceived as a gray-haired practice; the younger generation simply isn’t studying data modeling, nor is there much new research being done.
Louis: I can reduce most of what Paul said to "time" and the rest to "respect.” Time in that no one has time to do things right but seemingly infinite amounts of time to do it over. Getting it done is way more important than getting it done right. The funny thing is that getting it done right might add 20-30% to the overall cost of a project. The only real problem is that to do it right, you have to know what right is.
This leads me to respect. As a database professional, I’m almost always outnumbered in any design session. I sit and listen about this new technology or that new pattern as if it’s a new religion. And then you have database design and implementation, which is largely based on a 30-year-old theory that is extremely solid. Although it has had lots of exciting technological improvements, database design is still fundamentally based on Codd's 12 rules for an RDBMS. So you have people excited about their new stuff, and they have to do some of this other work in databases, and it seems so boring. When I took my first job in databases, my best friend told me that it would be horribly boring. Seventeen years later, here I am.
Q: What are the most common mistakes that you see?
Paul: Ha! I thought this was a short interview! Here’s a quick rundown of common mistakes I see: tight coupling with ad hoc SQL, an extreme reverence for the “No Nulls“ doctrine without regard for the physical implementation, over-complexity, poor design of hierarchies, and thinking that denormalization will yield performance.
Louis: Tight coupling is the worst of all, and it’s the one that causes the most discussion. Paul and I are big fans of using stored procedures for the access layer because it lets you encapsulate queries in a way that is very easy to manage and test. But whenever I blog about stored procedures, it opens up the most heated battles. I saw where Paul had blogged about stored procs a while back, and there were 41 comments.
Initially, the data-interaction layer doesn’t give you a major gain. In fact, it almost always slows down development. But over time, as the magnitude of data in the database grows and grows, you have to start making changes to the database to improve performance. That’s where things get tough.
Paul: It comes back to what Louis said before: time and respect. Projects aren’t given enough time for you to do great database design. It’s still true that if the database is designed well, the application is easy to code. But no amount of code can correct for a poorly designed database.
Q: Getting the upfront design right can save organizations so much time, effort, money, and headaches. Any tips for how database designers can get the time they need to do the job right?
Paul: I believe that every day spent on design and architecture will save 90 days of development and maintenance.
Louis: I agree with Paul, although we quibble over amounts. There is no question that you will save a lot of time in the future by having solid design and excellent architecture and by knowing everything your system does when you are finished implementing.
Object naming and a good set of functional documentation are also tremendously important. There are so many systems out there with data structures that have weirdly named columns that cannot be removed because the person who created it is gone and no one is willing to delete it unless they are sure it’s completely useless. I like to think about "future me," and the nicer I can be to him, the better. I like to think that by working harder and doing a better job in the present, my future self can spend more time behaving like the Maytag repair man. In reality though, it leads to more work because every organization that gets something that works will want more, not less.
Paul: I’ve seen it over and over again: A poorly designed and tightly coupled database is the root cause of massive expense. And management lives in fear of touching the database due to the domino effects of broken objects. Good management recognizes the value of design, but good management is hard to find.
Louis: It just wouldn't be a good developer topic without some attack on management. To be fair, though, you can't expect managers to know everything. And I will fully admit that if I did not have experience with the failures of poor database design, I too could probably be easily swayed by the flashy new widget that the programming team uses as an excuse to no longer need database design or good database coding. There are lots of cool tools out there, but SQL Server works best with a properly designed relational database—even if it takes a bit more coding to get done.
Q: What advanced concepts and techniques will attendees learn in your database design seminar that they wouldn’t get in a typical workshop?
Louis: The funny thing is that I find it hard to call any of it "advanced"—there’s nothing that we will cover that you are going to need your college data structures class knowledge to understand. It is all largely straightforward stuff. What is probably most different is that we are going to cover more than the three normal forms you should care about.
Paul: I believe there are advanced techniques that complement normalization, such as generalization and data-driven designs, that can be the difference between a workable solution and a great solution. It’s not a matter of higher forms of normalization, but different patterns—still normalized—that add extensibility and flexibility to the design.
Q: What approach do you take in presenting a seminar together?
Louis: We’ve done several presentations together, largely because we’re among the few people who tend to talk about database design. So many presentations at conferences are about the technology. And while these sessions are extremely useful and necessary (I will attend as many as I can myself), many attendees have never really gotten any education on design. It’s a shame because to get the most out of the technology, you really need a solid understanding of why the database server is built like it is. Topics like normalization make topics such as indexes a lot more understandable.
Paul: Two presenters bantering back and forth with lots of participant is a very different session than the typical single-presenter conference session. So if you come to our post-con, know what to expect: It’s a lot of fun, but if you’re looking for someone reading PPTs, you’ll be disappointed.
Louis: Participation is the key. We’re going to pass out paper for people to attempt their own models based on scenarios we set up, and then we can discuss them. Having some time to get your hands dirty and do some design along with us will be very helpful. I know that was part of how I learned when I attended my first design seminar 14 or 15 years ago. I hadn't yet built my own "good" design; I had only built a few hideous designs for a company I had worked for, which were great for learning what I did wrong. But getting things right along with my classmates was very cool, and having what I did wrong pointed out was nice, too.
Q: Do you usually agree with each other? Any points where you take different approaches?
Paul: Well, Louis agrees with me more with every presentation (LOL). We have different backgrounds, so we are each reacting to the different worst practices we’ve battled against. I’d say we agree about 80% of the time. But it’s that 20% that makes it fun—iron sharpening iron. I know I’ve changed some of my views due to Louis.
Louis: Our backgrounds are very different. I had never worked with anyone who took design too seriously, so I spend a great deal of my time preaching about doing things better. Paul, on the other hand, has several cautionary tales about people who were drunk with normalization power and ended up with more tables than were necessary. So sometimes it seems that we are disagreeing when we are actually agreeing.
Paul: I like how you put that, Louis. We’re both pointing to the same middle ground, but attacking it from different flanks.
Q: If you could make one statement about relational database design to leave with people considering your seminar, what would that be?
Louis: The big takeaway should be that normalization is not a process you use to validate your model. It is a group of standards that you apply as you work to build a data model. It’s a natural part of the process that everyone does to some degree. You would never find any database, even designed by the newest newbie, that has only one table. But the techniques we cover will help you understand why you are adding more tables to your database and what the benefits are so that you can explain it to the naysayers who just want to get back to building a UI.
Paul: Louis is right. Normalization isn’t a set of rules to validate the design—it’s a way of thinking. If database design is your thing, or you’d like it to become your thing, then this is the year to sign up for a post-con. We’d love to have you join us as we spend a whole day digging into database design excellence.
Hear more from Louis, Paul, and other SQL Server experts at PASS Summit Unite 2009 in Seattle, WA. See the lineup of pre/post-conference seminars, review the full conference agenda, and register today.
Louis Davidson, data architect for the Christian Broadcasting Network and NorthStar Studios in Nashville, has more than 15 years’ experience as a corporate database developer and architect. A SQL Server MVP, he writes the DrSQL blog and is the principal author of Pro SQL Server 2008 Relational Database Design and Implementation (Apress) and other database design books.
Paul Nielsen—instructor, consultant, database design expert, and SQL Server MVP—is the author of the Microsoft SQL Server 2008 Bible (Wiley) and developed the BrainBench SQL Server 2008 Programming Certification. He was also the design subject matter expert (SME) for Microsoft Official Course 2784: Tuning and Optimizing Queries Using Microsoft SQL Server 2005 and was the enterprise data architect for Compassion International.