Physical Design   «Prev  Next»

Lesson 9 Evaluating a relational database
Objective Interpret this sentence: "There are no wrong databases, just useless ones."

Evaluating Relational Databases

After a lot of research and a fair amount of sweat, you've come to the end of the database design and implementation steps of the database life cycle. Your database looks good on paper, you've implemented it in SQL, you've avoided the common design mistakes, and accounted for international business data. But the nagging question remains:
Is the database correct?

There are two aspects to that question: the technical and the practical. The answer to both parts is this: your database might be in third normal form and ready for data in any language from Swahili to Klingon, but if users cannot use the data in the database to answer their questions, the database is useless.

Design Process

Throughout the design process, you should constantly ask yourself if the database you are creating meets your customer's information needs.
  1. Every table should represent a business object that can be combined with other tables to produce information.
  2. Every constraint should help users enter correct data.
  3. Enforce referential integrity to ensure table data remains consistent.
  4. Create views that combine table data into actionable information your users can use as the basis for important business decisions.

Database Performance

A database that runs a bit slowly but answers the questions that need answering is infinitely more useful than a lightning-fast database that contains no actionable information.
Hence the statement: "There are no wrong databases, just useless ones."

Performance Anxiety

Many developers focus so heavily on performance that they needlessly complicate things. They make a simple solution complicated and harder to build and maintain all in the name of speed. They denormalize tables to avoid using any more tables than necessary and they build business rules into the database so they don’t need to use stored procedures or other code to implement them separately.
Modern hardware and software is pretty fast, however. Often these CPU-pinching measures save only milliseconds on a one-second query. Think hard about whether a convoluted design will really save all that much time before you make things so complicated that you can’t build, debug, and maintain the application. If you’re not sure, either make some tests and find out or go with the simpler version and change it later if absolutely necessary. Usually performance is acceptable, but a database that contains contradictory data is not.
I once worked on a huge database application where a simple change to the data might require five or more minutes of recalculation. After about three days digging through horribly convoluted code and database structure, I found the problem. The original developers had used a bunch of tricks to perform calculations in some sneaky ways to save a little bit of time here and there. Then they had done something really silly that made them perform the same calculations again and again more than a hundred thousand times. They were so busy worrying about tripping over the blades of grass that they wandered blindly into a patch of poison ivy. I managed to speed things up a little, but a lot of their time-saving tricks were so buried in the underlying design that there wasn’t much we could do without a total rewrite.
The moral is, you don’t need to be stupid about design and ignore obvious chances to improve performance, but do not be so focused on the little things that they cloud the grander design. First, make it work, then make it work fast.

The next lesson wraps up this module.