In many organizations, relational database design is an afterthought or a lost art. But Michael J. Hernandez considers it an important undertaking -- one in which core principles still bear deep consideration. Hernandez is the author of Database Design for Mere Mortals, which was published in its third edition in February 2013.
A long-time database developer, Hernandez has worked as a program manager at Microsoft and an instructor for companies such as AppDev Training Co. and Deep Training. Originally published in 1996, his book focuses on database design and configuration practicalities -- from requirements-gathering interviews on.
Hernandez champions the cause of flexible but well-structured relational databases that can underlay quickly launched Web applications but that support data growth and business changes. In a world that often asks if data modeling and full-fledged database planning and design are really necessary -- can't we just start coding? -- his message has always been: Don't shortchange the design process. SearchDataManagement spoke recently to Hernandez about database design best practices. Excerpts from the interview follow.
In your book, you suggest that data professionals are often in too much of a hurry to start coding, without doing the requirements gathering that is part of good relational database design. Why is the requirements gathering stage so important? And how should it be approached?
Michael Hernandez: Many times, people make a lot of assumptions and then create the database and roll it out. Later, there is pushback from the users. The fact is it's important to have conversations with the business users ahead of time and get a sense of what is going on and what they need. That informs a lot of what is going to be built.
Basically, you want to be talking to different individuals at different stages [of a project] so that you're sure you're capturing the proper concepts [and] that you understand the ideas they have about their business. So, I emphasize interviews.
People that don't discuss relational data structures with users often expect to fix things later with coding … and they end up with just a mess.
Michael J. Hernandez
To do this right, you need to understand the different relationships of aspects of the organization and its processes. As you work on the relationships, you find the details and concepts that have to be represented in the database. You have a conversation with the users and understand what they need. Then that informs what is going to be built.
What are the ways toward effective interviewing?
Hernandez: Interviewing for database design isn't an exact science. But it is a skill that can be learned. The people who do it have to have very good analytical skills and good people skills. You ask people how they define their daily work. You ask them what is the first task that they do in the day and [about] what they are dealing with conceptually. Usually they're dealing with customers. So you ask, "What is a customer to you?" The answer is different for different companies and different departments within companies. You have to learn their perspective, and their semantics.
High-level concepts like customers, visits, schedules, orders or tasks -- those are main concepts that have details around them that you have to record.
And what are some things that stand in the way of efforts to do the requirements gathering that forms the basis for good database design?
Hernandez: It's all about time. Today, people want to do it quickly. They want to get it out there, and then they'll see what happens. They say, 'If we get pushback, we'll fix it as we go.' To me, that is such a bad way to do it. You can avoid a lot of headaches and problems ahead of time if you just invest the time to plan.
For information on SQL Server database design
Find guidelines for database index design and optimization
Catch the best of SQL Server indexing strategies
That's one of the things I tell people: This is not a waste of time. You are investing the time to go through this process in a considered manner and to create a quality data product that probably has a higher success rate than if you just rushed right through it.
So, personally, I am not a big fan of Agile design, Agile computing and the whole Agile concept. I think that is the opposite way than the one we should be going. A lot of people try to shortchange or avoid interviewing. But it drives what you design. It's what makes it successful, what makes it usable by the people that are going to work with [the data].
To make sure you establish the proper specification, you need to revisit the design with the users and [business] managers. Users and managers are going to have different perspectives on how the data is used. That's why discussing the evolution of the relational data structures with them is useful. People that don't do that often expect to fix things later with coding. What they end up with is just a mess -- a railroad wreck.
This was first published in October 2013