CPrint's job management database was home-grown. They had built it using a simple commercial database product and used this database to track details of clients and the requirements of each print job.

It was a very simple, single-table design with a record for each job. Each time a new job was entered, it was necessary to re-enter all the customer details, even though a large part of their business was repeat business. Because of this structure, if customers changed their contact details it would be necessary to manually find every record and edit them all.

Addresses were not recorded in a consistent way, and a large number of additional fields had been added over the years, many of which were seldom used, or else had been used in an ad-hoc manner to record other notes.

These kinds of limitations persuaded them that it was no longer a good fit for their needs, and they were keen to replace this with something more professionally designed and built, that supported the way they currently work.

Over the course of a few meetings, and a few mock-ups, we designed and agreed a more appropriate data model. Database objects were identified to represent distinct entities such as business, site, contact, product, job etc with records linked together to represent any one job.

Now there would be only one data entry for each business client or contact working at that business, and when details changed (like telephone numbers or staff changes) they would only require one update to be made. Fairly basic database design really, but a large improvement on what previously existed.

We also reviewed, reduced and simplified the number of fields in the database, leaving those that were necessary and sufficient to represent the majority of jobs, and provided free text fields to record unusual requirements or special instructions.

The solution was built in Excel, with tables for each database object, and jobs represented by linkages between tables representing orders, customers and products and so on. The user interacted with the database via a number of simple-to-use forms, making the task of data entry as easy and efficient as possible.

I further added ribbon commands to print out job details, delivery notes, and other useful paperwork, directly from the database. Finally, I wrote software to help migrate the client's legacy data into the new structure and provided support to ease its adoption and transition into use.