Old data migration from the system to the new web application proved to be the biggest challenge. It was difficult due to lack of technical documentation and solutions that are currently out of use. The first version of Signium’s system was created about 25 years ago and updated a few times after that.
The transfer of data was essential for the proper functionality of the new web application. Our aim was to migrate the data and make the web application work efficiently. As with any mature product it contained inconsistencies, non-existent references or “interesting” solutions. Some of them, however, proved to be highly innovative and impressive for those times.
The first step was to separate important data. We reduced some tables by half, for example from 40 columns to 20. A significant part of the tables wasn’t useful for us, but due to the lack of documentation, we had to be in permanent contact with the client, to analyze the operation of the old software, step by step, table by table, and to assess what data we would need. Having selected the tables we were interested in, we rebuilt their contents to JSON. Then we wrote a script in Ruby, which flew over each of them and matched the data to a previously designed new database (PostgreSQL).
As mentioned before, a huge part of the data contained left references to previously deleted elements. It also often combined tables by strings, not IDs. The old tool had many open fields for self-entry. In the new application, they had to be replaced by values taken from the dictionary to avoid lack of consistency in nomenclature. It ended up mostly in plucking all possible variants of a given column and wrapping it in a big switch case.
Another challenge was to process attachments in the database. A certain type of record allowed files to be attached to it – they were saved as BLOBs in one of the tables. We were forced to use an external program (OLE Express) to export files. Unfortunately, despite following the program instructions, they could not be named according to the references held by this table. We also had to sort the records and give the files a name in the form of an iterator (1,2,3 etc.). We wrote a simple SQL that combined table records and attachments with the parent table records in the correct order. After trying to transfer them to the new application, we noticed that they fell into the wrong places. It turned out that the old application had an error related to attachment translations. The main version correctly matched with the given record, while the translation contained an empty reference. This caused a simple error on our side because instead of the left join required here, we used the inner join. It may sound trivial, but it was not easy to orientate, because in this case we operated on the ID itself.
However, the most important thing is that we managed to overcome these challenges!
The application should allow users to filter the results of the extended database by many different categories like location, nationality, company and many, many more. So its UX and UI should be at the highest possible level. Moreover, we have cooperated mostly remotely during the project using emails and calls for communication and Jira for task planning and reporting.