 |
|
|
|
|
|
There is a common belief that database design is done by the database administrator (DBA), while
program design is done by programmers. This may seem like a natural division of labor, but it is
actually quite unfortunate. Applications are made up of two equal and interrelated parts: data and
processes. Neither has any purpose without the other and weaknesses in the design of either (or their
intimate relationship) will be reflected in the entire system.
Design of the database and design of the total application require parallel efforts with frequent
validations of each against the other. An elegant database design that requires awkward programming
will be a maintenance nightmare. Atightly integrated and effective delineation of program modules
can be rendered useless if the data needed by those modules is not structured for convenient access.
Even worse is the discovery, as an application nears its final integration, that some data needed for
a particular function isn’t available or that some portion of the database has no means of being
populated or maintained. It happens.
The solution is for the entire development team, analysts, programmers, and DBAs to work together
with the system’s eventual users to mutually discover both functional and data requirements and
coordinate their implementation. DBAs need to gain an appreciation for modern programming
constructs, while programmers need to understand the tenets of good database design.
This chapter isn’t intended to teach object-oriented programming concepts to DBAs, but it does
provide developers with the basics of relational database design and knowledge of the additional
data management facilities of the Oracle database.
Some parts of application development (systems engineering) are methodical and almost scientific.
There are straightforward ways of performing certain common tasks that can be readily adopted to
solve specific problems. Database design tends to have more guidelines and ideas than absolute
rules. What rules do exist are always presented in the context of when they should be broken. This
chapter looks at the rules for normalized design and then turns immediately to the reasons and techniques
for denormalization—intentional (and well-considered) violation of the normalization rules.
So consider database design to be more art than science. Don’t ever attempt to argue that your chosen
layout is the one correct design; further, be extremely suspicious of anyone else (DBA, consultant,
manager, or Peruvian llama farmer) who insists that his or her approach is the only right solution.
The only absolute rule of database design is that there are always alternatives. That is not to say that
some alternatives are not better than others; clearly there are wrong designs. The point is that the relative
merits of design alternatives are only evaluated in the context of how the database will be used. These
“how” issues are the province of the process designers and programmers. No DBA can develop an optimal
database design without intimate knowledge of the way all of the programs will need to access the data.Even with knowledge of the programs’ data needs, it is still necessary to evaluate alternatives in light of
multiple often-conflicting priorities. Performance is generally one of the priorities, but then it is necessary
to determine which of the programs’ performance needs are most critical. Modifying the database design
to optimize a particular access will inevitably make some other accesses less efficient.
For an order entry system that takes in thousands of small, online orders each day, it may be most critical
to optimize the database to accommodate that specific process, even though reporting, inventory management,
shipping, and other related functions are either made slower or more complex. Another order
entry system that receives only a few orders per hour for thousands of line items that have stringent
availability requirements might be better designed around the needs of demand forecasting and analytics.
Let me offer one war story. I was the lead database designer for an order entry and customer service
database for a large retailer in the early 1990s. The eventual system, when it went into production,
included the largest known Oracle database on Unix in the world. I had an idea for a rather radical
physical design change that would dramatically increase the availability of data in the event of a disk
failure. (With the size of the total database using the 2 GB disks of the day, with no available disk
mirroring, we calculated that we could expect an average of one disk failure per month.) This change
would reduce recovery time by at least 50 percent and allow continued access, during most failures, to
90 percent of the customers’ data while the 10 percent was being recovered.
The denormalization idea that I was considering would place some additional programming requirements
on most of the common modules that had interfaces to the database. The design choice would also make
certain types of access impractical, if not impossible. I spent two full months coordinating with each
analyst from every subsystem team to make sure that there were no requirements to access customer
order history data except within the context of a particular customer. Finally, everyone agreed that they
had no such requirement. I made the database design change, and all database access modules in the
system were modified to access the redesigned table structures.
All was well until about a month before we were ready to begin stress testing with an almost fully
loaded database. A developer had been experimenting (without requirements or specifications) on a
really interesting set of reports that would allow management to see the popularity of products across all
customers. Nobody knew about this “skunkworks” operation until he brought me the SQL queries and
asked to have access to the stress test database that was then about half loaded. I looked at his queries
and told him they couldn’t run. He was indignant and insisted that I was just a consultant and only his
management could decide what was allowed to run. I tried to explain that I was not discussing permission,
but practical reality. He wouldn’t take no for an answer, so I let him start one of the reports before I left
on Friday. When I returned on Monday morning, he understood my distinction between “can not run”
and “may not run.” I killed the still incomplete query.
So who was to blame? Not him—his idea would have been a great tool for management and just
predated an analytical warehouse in that shop by about three years. Not me—I built a database
designed to meet all of the application requirements and provide extremely high availability given the
technology of the day. The blame (if that concept even applies) was only in the project management that
allowed the mismatch between program/functional design and database design; neither took the other
fully into account.Only by understanding the many conflicting requirements of a system, evaluating their relative
importance and then understanding the implications of the trade-offs can the design team achieve a
final, optimized design.
Database Design Phases
Database design is not a single effort. Typically the design of the database goes through multiple high-level
phases. At each step it is necessary to review the current state of the database design and compare it to
the current state of the processing design. At each phase the comparison becomes more detailed and
exacting.
Conceptual Database Design
There is no significant reason to separate conceptual design from logical design. Some designers or
methodologies move some preliminary activities into this phase, such as identifying key business
drivers and high-level purposes of an application (and its database). The output of such a preliminary
phase would typically be a few paragraphs (or, at most, pages) that describe the opportunity for improving
a business process or to provide a new service with an estimate of potential benefits and a statement of
the scope of the effort. This can provide a useful perspective to set boundaries on the rest of the design
processes to prevent expanding beyond what can be reasonably accomplished. |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
