I’ve been searching for a definitive answer to the question: “What is the cost of poor database design?” No doubt you have all seen the cost/benefit graphs for fixing application problems and the pyramid showing how up to 90 percent of performance issues in a running database are SQL and index related, but what about the cost of building an application on a poorly designed database that forces poor SQL usage?

No doubt most of you have seen them; databases ported directly from a flat file or legacy non-relational system where no concept of normalization was ever applied. Databases deliberately designed to be flat files, databases designed with deliberate multiple recursion in tables (“flex fields”), databases where the burden of referential integrity was left to the application, and databases that make no use of advanced features such as index only tables, partitioning, global temporary tables and materialized views (or summaries if you prefer.) Don’t even get me started on the forced use of improper technology such as object oriented design, VARRAY and nested tables where they shouldn’t be used.

It is surprising with the predominance of these types of table design flaws on basic tables in relational databases that a study somewhere hasn’t posted the cost of these bad design decisions on TCO for applications. Of course maybe it is so predominant that everyone just assumes you are starting with a badly flawed database design.

What is really sad is that with the application of a few design principles such as normalization and then selective denormalization and the proper use of advanced features truly performance oriented databases could be built. With simple tools such as the Toad® Data Modeler improper designs can quickly be corrected. Use of the indexing and partitioning wizards available in Toad would also help to show where some of the advanced features can be applied to a design.

Of course in these days of “The 2-Day DBA” it is asking a bit to expect DBAs to be fully conversant on all new features, especially as they apply to design of a database, not to mention expecting designers and application coders to understand them.

So what can be done? I suggest all DBAs read over the new features guides and references and concepts guides to get an understanding for their databases what features are available. Managers need to get DBAs involved with the application design from the start, not wait to call them when it performs badly. Finally, if you aren’t familiar with normalization and denormalization, get some of the classic design texts that will provide you a good basis for these concepts and study them. If we apply good basic design rules to our systems and use performance enhancing new features there is no reason why the database should be the source of performance issues.