Databases Reference
In-Depth Information
TIPS AND INSIGHTS FOR DATABASE PROFESSIONALS
•
Tip 1. Use a strategy.
Many very intelligent database designers don't come close to
maximizing the potential of their systems simply because they developed the database
design in an ad hoc manner. Using a strategy for exploring the possible database design
choices will definitely help the practitioner develop a better design than a database
developed using ad hoc planning (i.e., nonplanning).
•
Tip 2. Start with indexes.
Chicken soup is good for a cold and the chicken soup
approach to database design is used by most database designers most of the time. It's
reasonable, and you can achieve “near optimal” result that are more than adequate.
Indexes are the most important feature and as a general rule are the most broadly
applicable. A database with only indexes can still be perfectly functional. A database
with all the bells and whistles, but that lacks a reasonable set of indexes, will usually
be unusable.
•
Tip 3. Think about the strategy that is most appropriate for you.
Dependency anal-
ysis is very hard for most human designers to use effectively. Pain first is a good strategy
to use if you have a complex environment with a lot of data and feel you really need to
design with a significant amount of data to get a good feeling for how the system will
behave. Large volumes of data will mean large pain (i.e., time and waiting) when you
want to explore each new design attribute. If you are dealing with a smaller volume of
data during the design process, you may want to start with the impact-first strategy.
(Strategies for dealing with the problem of exploring design changes on a fully popu-
lated database are discussed in Chapter 11.)
•
Tip 4. Use design automation technology.
Many vendors provide design automation
utilities. This emerging technology is not yet able to provide recommendations that are
as high quality and reliable as the very best database designers. However, they often can
come quite close, and can form an excellent basis for a database design that the human
designer can refine. A detailed discussion of these utilities can be found in Chapter 12.
Oracle provides the Oracle SQL Access advisor, Microsoft SQL Server provides the
Database Tuning Advisor, and IBM DB2 has the DB2 Design Advisor. All of these ven-
dors provide these utilities as part of the basic database product, and no additional pur-
chase or installation is required.
•
Tip 5. Iterate.
Database design is as much an art as a science. The complex interactions
do make this process very difficult. Don't be afraid to tinker and to go back and recon-
sider previous decisions, especially after making changes in other areas of the system. As
long as you don't let your iterations become a habitual unstructured ad hoc design anti-
process, you'll end up with a better design than you would achieve using a single-pass
strategy. Force yourself to make the first pass a very structured process and that will
allow you to use more freedom and flexibility in the iterations that follow.
