Computers: Confessions of a database designer: The key to a successful system is bridging the gap between belief and reality. Kay Ewbank reports

Click to follow
In spite of the ever increasing quality of database software, I do not know that I can wholeheartedly recommend the career of database designer to anyone of a nervous disposition.

The theory is fine, it all sounds nice and simple, but unfortunately, the manuals never quite cover things such as how to deal with an order for two out-of- stock items and one discounted product, using a combination of a postal order, a dollar cheque and a credit note, all of which comes to 50p short of the total minus the VAT, which the customer has ignored as irrelevant anyway because they live in the Channel Islands.

For those considering a foray into databases, here are a few situations to avoid. First of all, beware of political in-fighting. There is a terrible tendency for people to see the database as a way of acquiring power, hence the proclivity for whispered consultations in the lift, by the coffee machine, or in the Italian restaurant down the road. Quite apart from the suspicions raised about just what is going on, this has various drawbacks, not least that restaurants are so unreasonable about you removing tablecloths, even those covered with the only known sketch of what the system should do.

It also makes life difficult for the poor old developer - when one group has sworn you to secrecy over the time of the next planning meeting and the opposition interrogates you about when it is happening, what do you answer? The database will be more balanced and better received if people feel it is designed by them, not forced on them.

Try to keep people's expectations realistic. If the current system consists of a spiral bound notebook and a biro, then the chances are a little remote that you will be able to create in your lunch hour a complete working system that gives the answer to the question of life, the universe and everything. To avoid despondency, split the project into manageable chunks that can be put into operation - get the stock control working, activate the name and address list so people can carry out mailings and so on. That way it will be obvious the project is moving and people (including you) will not get disheartened.

Try to be clear about what really happens rather than what the management thinks should happen - there is usually quite a large gap between belief and reality. Managers will tell you that no order goes ahead without written authorisation from the head of department. Then you will speak to Fred in the warehouse, who tells you that he always starts packing the ABC Ltd order as soon as Jim gives him a call, 'cos otherwise they miss the Portsmouth ferry'. This is a tad tricky to write as a logical condition, but without such flexibility, the whole process can grind to a halt - and the database gets the blame - 'It always worked in the old system . . .'

Find ways to handle the true co-operative nature of the company. For instance, each department will undoubtedly have a different set of names and codes for customers, products and probably days of the week. Do not try make sense of this. Match the sets and try to avoid blame over which is chosen as the official version of the truth.

Stay flexible. No matter how wonderful your creation, three days after they start using it, someone will find 1) a vital piece of information missing from the forms; 2) a question from the managing director that cannot be answered using the current reports; and 3) a name that is too long to fit into the space allocated.

There is a temptation at this point to jump up and down, shouting about how often you have asked about that point. Instead, count to ten (twenty, one hundred, a thousand . . .), then just get on and make the changes. The only way a system will really get developed is through use, so start using it as quickly as possible and make the changes according to reality not ideals. As a side issue, never get rid of a field, form, report, or query. If someone asks to have it removed, someone else will undoubtedly ask to have it put back a couple of weeks later. Just hide it for the moment.

Once you have got a little way, you have obviously got to test your system. The important thing here is to avoid being silly. Resist the temptation to add a fake record for your boss with a salary of 2p and a personnel record of 'must try harder' - he will undoubtedly see it and not see the funny side. The same thing applies to giving your least favourite customer an overdue invoice of pounds 500,000. This is especially important when you are testing the system once it is in use. Be boring, get some fake records that you will remember to take out and it will be obvious if you forget. Mr Zebedee of the Magic Roundabout Co Ltd has never yet to my knowledge become annoyed by seeing invoices addressed to him.

Do not believe all the database tells you, especially at the beginning. Probably the most frightening part of designing database systems is the way the answers they produce are instantly treated as though carved in stone. If a new and inexperienced member of staff told you that the company had sold 20 million widgets this month compared to 2,000 last month, I am sure you would do a little checking before placing the order for more. So why believe your new electronic clerk, the database?

It is almost certain that in a large system, some of the data will have been entered incorrectly. Add to that the possibility of misunderstandings about what the answer should be, as well as errors of logic and you are bound to have some problems to be ironed out. The solution is simply to run the paper equivalent for several months in parallel with the database. I know this is tedious, irritating, makes you wonder why you're bothering . . . but the alternative is ordering 10 tons of widgets that will at current estimates last 833 years.

Finally, prepare yourself to be a victim of your own success. Once the database is working well, people will forget completely how bad things were without it and will only ever complain about its shortcomings. All right, so it takes five hours to run a particularly complex report, but try to remember that the same task used to take a couple of weeks sorting through paper.

If the complaints get too loud and you cannot cope any more, there is a simple solution. Tell them the system needs maintenance and shut it down for a couple of weeks. It is amazing how people appreciate it once it is back.

(Photograph omitted)

IndyBest product reviews are unbiased, independent advice you can trust. On some occasions, we earn revenue if you click the links and buy the products, but we never allow this to bias our coverage. The reviews are compiled through a mix of expert opinion and real-world testing