Major database restructure for pump manufacturer

To meet our pump manufacturer client’s latest requirements, we realised we needed to do a major redesign of their database structure. We understand that getting the database structure right before developing the application is key to the future success of that software.

The application currently defines and captures the results of one set of tests that are done when a pump is first manufactured (built).

The new requirements are:

A) Process multiple repairs for existing pumps

B) Process orders for spare parts

C) Record a history of Build, Repair and Spare “Actions”(Build, Repair or Process Spare) over time

In order to cater for for this, we had to split the existing “Pump” entity into 2 new entities “Item” and “Action” so that we can describe Actions to be performed on Items e.g. Build Pump 123, Repair Pump 123, Process Spare Part JH78

With our expert skills in Entity Relationship Modelling, we built a normalised relationship model for the new database that captures all the information we now require.

The remaining challenge was to create a program to automatically migrate the existing data from the old structure to the new. For this, we developed a sophisticated SQL script which processes the old database:

  • Renaming tables and creating new ones
  • Adding new columns and renaming existing ones
  • Migrating data to new tables
  • Populating new columns

Our solution is fully automatic and reliable and can be repeated during testing and, finally be run when the new application goes live.

Being experts in relational database design, you can rely on us to provide a solid and flexible relational database structure for your bespoke application.

Leave a Comment