Advice for Database Builders and their Managers

Don't let short term savings turn into long term disasters

Friendly products like Access can encourage people to dive in and build a database without careful planning. Office databases created by beginners sometimes work out ok. In other cases there can be serious problems down the line. Paying to fix a badly designed database it usually going to cost more than paying to have it built right from the beginning. If the owners of those databases had developed them properly at the outset they might have saved a lot of money in the long term. They end up investing so much in the broken database that they daren't justify the cost of writing it off. To give you an idea of the scale and cost of the problem, there are highly successful companies that make their money from writing incredibly complex SQL queries to make badly designed databases do something useful.

You can save money in the short term by putting a database together by trial and error, but in a couple years time, when your business is reliant on the data in it, you might find that some hasty design decisions made when you were a beginner mean you have to enter a lot of your data twice, and because of entry errors, sometimes the two copies don't match up. Problems, problems! You might end up paying for the design of a new database that works properly, and then paying to have all your data transferred as well. What a waste.

A badly designed database and a little bad management can turn bad business process into a liquidation. A broken database can lead to a broken billing system. If customers lose confidence in their bills, they don't pay. If the scale of the problem is large, disaster can ensue. Most small businesses don't have enough different customers for this to be a real threat, but some do.

It could be cheaper than you think to get your databases designed and built by professionals, and it could save you more than you expect.

Common Database Blunders

We've put together a few thoughts about the major thing you can do wrong when designing a new database.

The beginner's primary database blunder is 'bad keying'. e.g. It might have seemed like a good idea to key on client name when the database was built, but when you have twelve Simon Smiths on your books, it doesn't seem like quite such a good solution. To fix the problem you end up dragging huge chunks of customer data into unrelated queries, and they start to get complicated to write and run very slowly. This is why the big companies assign cryptic ids to customers, employees, products, contracts, support queries, etc.

Bad keys can cause problems when you decide what to put in each table. If you accidentally choose a non-unique key for your table, then it will end up being useless. This might seem obvious, but sometimes it doesn't show up until the table gets a lot of real data in it. It could go unnoticed for even longer if it's a table that you aren't really using. When you add new functionality, you can suddenly discover you have tables with duplicate keys that have grown unchecked for months - the result is chaos. For this reason you need to make sure that every table has a unique key.

A related problem is when data gets into a table that shouldn't. When rarely used data that should have been in its own tables with its own keys creeps into a heavily used table, performance suffers and you might even end up entering duplicate data when filling in tables.

Which leads us to another problem: duplicate data. If you have good keys, and have split your data into different tables wisely, you shouldn't have any duplicate data at all. The only duplicate data in a database should be 'by design' allowances, such as allowing a customer to have multiple addresses. Such things aren't really duplicate data anyway, they're part of good planning.

And so our final piece of advice is to think carefully about data items that you might need more than one of. It might be tempting to assume that a customer can only have one address, but it could cause you problems.

Let us know if you found any of this even slightly useful. If people are interested we might develop a more detailed database tutorial, with diagrams and step by step explanation of how to key and split up your data.