illuminaries...turning data into information

Community Paediatrics, St James' Hospital

Database Design & Data Migration

The Child Protection Database developed by Illuminaries for Community Paediatrics at Leeds Teaching Hospitals replaced two legacy dBase IV databases in use at St James’s Hospital and Leeds General Infirmary. The two databases had identical data structures but were not linked and the codings in use at the two sites had drifted apart over the 10 year life of the systems. In addition, the team based at St James' Hospital had started to use digital cameras in clinic and whilst the images for each episode were stored in separate folders, a variety of naming / cataloging conventions were in use by different clinicians.

A new, conceptual data model had been designed by Illuminaries and was implemented in a SQL Server 2000 database. A component of this was a Document Management System (DMS) to provide a central repository for letters, reports and images relating to each epsiode.

The first phase of the data migration process took the form of comprehensive data quality checks, undertaken by Illuminaries. With the close co-operation of members of the Community Paeds team, the dBase data was cleaned and remapped to the new data model. This process ran in parallel with the development of the new front end, browser application.

Data migration tools were developed using a combination of MS Access and SQL Server Data Transformation Services.

The digital photography files were catalogued by episode number into an Access database and migration tools written to re-catalogue these into the DMS in the new database.

Finally, the clinical reports previously stored across a number of fields in the dBase system were re-generated as html documents and registered in the DMS.

A number of trial migrations were undertaken, each one picking up new issues and introducing refinements from previous attempts. The new system was rolled out live at at the two sites on different days, with about a four week gap in between.