10 Database Design Best Practices
Page Contents
- Focus on the data, not the application
- Leverage the power of your database
- Include DBAs in the design phase
- Use data models
- Consider different data interaction strategies
- Reuse good ideas
- Index foreign database keys
- Pick database keys that support partitioning
- Model with multiple perspectives
- Don't ignore the data access layer
With enterprises looking for new ways to use data to gain competitive advantage, database design is now more important than ever. Here are some best practices that will result in a well-designed database.
Focus on the data, not the application
“Applications come and go, but data is forever,” said Justin Cave, lead database consultant at Distributed Database Consulting, noting that he frequently sees programmers designing a database to cater to a specific application without considering future data needs. While they are often under pressure to deliver a database in a relatively short period of time, Cave said taking design shortcuts to help meet a deadline can make it much tougher to add new functionality in the future.
“If you properly design a database upfront, it is going to work for everybody. If you skip some functionality because ‘we don't really need that,' you can count on someone needing it in the future,” he said.
Leverage the power of your database
Too many programmers “treat databases like a data dump or a black box,” Cave said, and choose to build database-independent applications. While there are instances where this is appropriate, it generally is not the best approach. “When you are not using the power of the database, you end up making things less efficient and harder to maintain,” he said.
Utilizing the database makes it relatively easy to reuse design elements and add functionality. “But if everything is built into the application, you'll have to rebuild validation and a lot of the logic and you'll never get the same information,” he said.
Include DBAs in the design phase
“There are so many ways that data gets used, it's hard for someone whose task is to ‘build X' to envision that,” Cave said, adding that it's not uncommon for developers to wait until the final stages of development to interact with database administrators. The result may be a lack of attention to details important to DBAs, such as naming conventions which make it easy to identify objects contained in the database. Given that DBAs may not have much time to spare to work with developers, Cave suggested it's a good idea for developers to “learn to think like a DBA.”
Use data models
Despite widespread agreement that software engineering is important,many programmers forego data models, said Michael Blaha, a partner at Modelsoft Consulting and author of seven books including “Object-Oriented Modeling and Design.” Noting that “data is the memory of an enterprise,” he said, “Code is important, but replaceable — an organization can always rewrite an app or purchase a new app. There is no excuse for not constructing at least a summary data model.”
Consider different data interaction strategies
Many developers lack imagination in how applications can interact with a database, Blaha said. They may choose to use a database layer, hiding the database with programming objects that make database data indistinguishable from in-memory data. While this is a viable strategy, it has limitations.
“There are additional data interaction strategies,” Blaha said. “For example, dedicated methods can encapsulate data access. Some methods can be written as stored procedures. Others can be written in programming code. The remaining programming logic accesses the database via these special methods.”
Reuse good ideas
Rather than “obsessing on use cases” and trying to capture requirements from scratch, Blaha said developers can reuse ideas that have worked well in the past.
“For example, I often reverse engineer databases — databases of an application to be replaced as well as databases of related applications,” he said. “These existing databases often do not have an available data model. But a data model is implicit in the database schema and can be at least partially extracted with database reverse engineering techniques.”
Another good example of reuse is incorporating data patterns, Blaha said, noting that one of his books, “Patterns of Data Modeling,” explains many data patterns. “There are tried-and-true data representations that often occur and need not be recreated from scratch.”
Index foreign database keys
Every foreign key in a database should be covered by an index, Blaha said. While some foreign keys are covered by implicit indexes that are created as a side effect of primary and alternate key definitions, developers should create an explicit index for the other foreign keys, he explained.
“Database queries often traverse from a foreign key to its primary key,” Blaha said. “The resulting joins are efficient if primary keys are defined and every foreign key has an index. Just one missing index can degrade query performance by several orders of magnitude. It's hard to predict the traversals that might occur.”
Pick database keys that support partitioning
Even if you're not planning on partitioning now, the application will likely need to evolve to support it at some point. Bryn Rhodes, co-owner and software architect for Database Consulting Group, said selection of a key type that easily supports partitioning can mean the difference between a smooth transition and a difficult upgrade.
Model with multiple perspectives
Optimal structures for one use case are often different than for another, especially when considering access paths to data. Make sure you take multiple perspectives into account so you find these potential issues early, Rhodes said, and build structures that enable data access in various ways.
Don't ignore the data access layer
When modeling the structures, be sure to consider that everything you build is going to have to be consumed by a data access layer at some point. Avoid using proprietary extensions to SQL unless there is a very good reason to do so. And if you must use proprietary extensions, try to provide some insulation for them.
Ann All is the editor of Enterprise Apps Today and eSecurity Planet. She has covered business and technology for more than a decade, writing about everything from business intelligence to virtualization.
Public relations, digital marketing, journalism, copywriting. I have done it all so I am able to communicate any information in a professional manner. Recent work includes creating compelling digital content, and applying SEO strategies to increase website performance. I am a skilled copy editor who can manage budgets and people.
