Database Design Basics

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. A tightly 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.

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.

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.