|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 right?"
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!
Happy Days is my favorite theme song
I could sure kick your butt in a game of ping pong
I'll ace any trivia quiz you bring on
Throughout the design process, you should constantly ask yourself if the database you are creating meets your customer's information needs.
- Every table should represent a business object that can be combined with other tables to produce information.
- Every constraint should help users enter correct data.
- Enforce referential integrity to ensure table data remains consistent.
- Create views that combine table data into actionable information your users can use as the basis for important business decisions.
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."
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
The moral is, you don’t need to be stupid about design and ignore obvious chances to improve performance,
but don’t 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.