Have you ever encountered the problem of the database application that looks oh so pretty but it just doesn’t deliver? The aggregate reports are garbage and the performance is dodgy. It’s easy to enter data but a misery to get meaningful data out. Yes...well the first thing I would do is look at the database structure where the actual data lives. It’s a bit like looking over a house you are thinking of buying. If the roof leaks, there’s rising damp, the main walls are cracked and the plumbing drips and smells, unless you are prepared to rebuild you would be better off running away now.

• Do the majority of tables have a sensible number of columns say 30 or less?
• Do the vast majority of tables contain at least some meaningful data?
• Is the database normalised to at least, stage three? 
• Do all the main tables at least, have a primary key?
• Are the primary keys at least for the main tables, on integer or big integer fields?
• Are there logical, well thought out relationships?
• Object names do not contain spaces, brackets, arithmetic signs and other illegal characters?
• Is there any evidence of the existence of logical naming conventions?
• You do not find column after column of Nulls or empty?
• Are joins indexed?

If the answer to the first 6 or more of these questions is "No" like the house purchaser above, be very afraid! This tells you that this database” just kinda happened.” There was neither proper investigation of business flow nor identification of business objects. No business rules where ever discussed. No effort was made to produce a feasible specification – the developers charged off in every direction like mad explorers possessed of neither maps, GPS’s nor compasses. Either quote for a database rebuild whilst advising your client to ask their supplier for his money back or grab your hat and coat and scarper – unless of course you were the idiot that wrote the app! I often come across developers who can make your eyes water with 1000’s of lines of TSQL tapped out at the speed of light and C# or VB code churned out in a blur of fingers who do not have the faintest idea what normalisation is or what purpose it serves. They have heard the word but would not recognise the beast it if it were the size of an elephant next to the beers in the fridge. If you have time to dig a little deeper you will probably find all sorts of other indicators like unnecessary cursors in Stored Procedures probably named with the reserved “sp_” prefix, masses of SQL embedded in GUI code, crap security model and on and on........ Sadly, sometimes the size and proliferation of the bad quality indicators are in direct proportion to the size of the price tag and the supplier – quantity is not necessarily linked to quality.

Please note in this article I am referring to Relational Databases, not any other type. Ah I hear them say, "Oh but you sometimes have to de-normalise to improve performance." The need for this is far less than it was 15-20 years ago. Database tools have improved dramatically and with hardware your bang for the buck is better than ever. With data you have to start somewhere and that starting point should be database tables normalised to at least stage 3 - “each non-key field is dependant only on its primary key and independent of all other non-key fields” – and of course your well thought out database design will show where to join to the next table or tables. If your complex reports involve many calculations and or iterations of humungous chunks of data, by all means feed your normalised, processed data into your separate de-normalised reporting database or if the slowly processing reports are few in numbers, a few de-normalised reporting tables might be appropriate. You are at liberty to call your reporting database your Warehouse, OLAP Cube, Slice n’ Dice solution or whatever other name that is the flavour of the month. Starting off the basics with less than stage 3 normalisation will produce a nightmare of issues caused by redundancy, orphan records and erroneous relationships between data sets. Trying to build a database application without decent normalisation or indexing is like building a skyscraper in a swamp without piles or foundations - if it doesn’t fall over it will sink without trace.

Normalisation deals with problems of integrity, flexibility, performance and allows scalability. The best analogy I can think of is that of Airfix to Lego. Airfix is great if you want to build for example just one specific type of aircraft model to a 1/32 scale but the integral flexibility of Lego components allows houses, cars, ships, bridges etc to be built of any size. Normalisation involves a well thought out structure of Primary and secondary tables joined together from day 1 to enforce business rules at the lowest and most efficient and secure level. I groan in agony and frustration when I come across a well designed database wherein some rocket scientist has deleted the joins. His or her response to “why” is “I get error messages when I load data and when I delete records” indicating that said “developers” failed to understand the term “Relational Database” and its implications.