Background of the project
We are responsible for the migration of an over 10-year old legacy system (ERP). The reason for the migration is mainly related to business requirements and maintenance costs. Nothing new.
The CEO, who was also the database administrator who designed and created the database, says his database design is not corrupt and therefore not a destination for migration.
The software architect and I disagree with his statements. Some of the issues that we have addressed are:
- Multiple tables use business keys as primary keys. Some of these buttons are texts and accept special characters like & # 39; # & # 39; and & # 39; / & # 39 ;.
- Due to some issues in the past, some tables are used as Engine MyISAM, others use InnoDB.
- Many tables have compound primary keys with two or more columns that make up the PK (one of the tables that make up PK consists of 9 different attributes!), But there is no reason to keep this design. A single column with automatic increment would be sufficient for most cases and justifications / explanations for the why were vague or absent.
- There are several inconsistencies in the database, i. H.: Table father uses the attributes A, B, C, D and E as compound PK. Table Son has a FK from the Father table and replicates columns A, B, and C as a compound PK.
There are other reasons, but the above are the main reasons for the software migration process because they make the project more complex when mapping models and programming CRUD operations.
Please take into account that he is the CEO and currently he is also managing the DBA job, but he is completely out of the market as DB has been out of focus for several years.
The solution we accepted
We are migrating with a Strangler pattern so we can not make any structural changes to the database. The solution we found was to add a column that will be used as the primary key in the new software, and to transfer the responsibility for maintaining the PK of the legacy system to a particular service so that it will shut down after the strangulation is complete.
On the legacy side, we do not have enough resources to do the same thing. Therefore, we delegate the responsibility of updating the attributes used by the new software as PK using the pre-insert triggers and before updating to the database. Once the strangulation is complete, we only need to remove the triggers.
Not elegant (not to say the worst), this adds some workload to the server, but so far we have had no negative impact on the user experience.
The CEO / DBA found our approach outrageous, but failed to clarify why he opposes it, except that he believes in his belief that his design is perfect and should be maintained.
What I'm looking for
References. To show as well as possible that he is wrong or maybe I am.
Since database is not our main focus, it is possible that the whole approach described above may be confused, but it is difficult for us to look for case studies or the like containing information on best practices for database migration with Strangler Pattern. So I came here looking for insights from specialists.
Please let me know if this is really not the place for this question.