Blog‎ > ‎

Poor Database Design Or Lack Of Training?

posted 9 Aug 2012, 15:22 by Alistair Hamilton   [ updated 16 Sep 2012, 06:52 ]
database design and training
As an engineer, I get frustrated when I come across systems, computer or otherwise, that are poorly designed or where those systems are not properly utilised through lack of training or understanding on the part of the operator/business management. I came across another such system this week while working for a client.

To cut a long story short, my client had recently taken over another firm. I was charged with collating the firm's client details so that my client could do a mailshot to the customers affected. One small problem though. I was not allowed access to the original database. Don't ask me why, but my undestanding is, under Scottish law, because the firm in question was a legal firm, the data extraction had to be carried out by the The Law Society of Scotland. They had to contact the software developer of the firm's accounts package who then extracted the data in spreadsheet form, sent it to the Society who then forwarded it on to my client.

Now that the scene has been set, you'd think that doing a mailshot from a simple spreadsheet would be straight forward. That's what I thought until I looked at the data. A simple table of some 4000 records, with columns for the clients' names and address info, except it wasn't.
  • Clients' names were frequently truncated presumably due to the corresponding field within the database being limited to a set number of characters. This had forced users to enter additional names in the first address field.
  • Address information was frequently missing, or in the wrong column.
  • The clients' town information was often in the same field as their street and just as often missing altogether.
  • There were numerous, duplicate entries.
  • Clients' first and last names where stored together, but not necessarily in the same order and frequently included a salutation, though not always.
Records where clients had passed away for example were indicated by the flag 'DECEASED' or 'DECD' or sometimes 'DIED'. Lack of data entry consistency is always an issue with databases, but with no separate field to place these flags, users had entered them in a variety of different fields.

This type of limitation in the database software and the resulting poor data entry was evident throughout the 4000 records. Clearly there was an issue with data entry, that should have been addressed by proper training of the operators concerned. This is not unusual, particularly with smaller firms. I have seen it many times in legal SME firms. However, it is equally evident, from the limited exposure I have had to the data it generates, that the design of the original database software left something to be desired. Proper design would have eliminated many of the issues that the operators were obviously having.

If you are a small business owner who currently uses a database, please make sure you train your staff to use it properly. If you are looking to use some bespoke database package in the future, please make sure that its design meets not only your current needs, but those you may have in the future. At the very least, ask your supplier for a trial period giving you the oportunity to put the software through its paces.

Over the years I've not only designed and sold numerous database packages based on Microsoft Access but conducted many training courses on the subject. Perhaps the most common issue I came across during those training sessions is the complete lack of understanding with regard to the importance of the design process of the underlying data structures.

After this week's experience, I'm planning to post a few tutorials on the basics of database design that users of the database applications found in office suites such as MS Office and LibreOffice may find useful. These will appear over the next month or so, so keep an eye out for them.

Update 6 Sept 2012 - The first instalment of 'Basics of Database Table Design' is now available.