Relational Database Merging with Table Uniquifiers
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Merging two relational databases with the same schema into a single database is challenging, as it requires handling duplicate data, maintaining correct foreign key references, and assigning new IDs for identical rows with different IDs in the merged database.
Innovation Solution
A computer-implemented method involving table uniquifiers and a row merging procedure to identify matching rows, assign new IDs, and update foreign keys, ensuring data integrity and uniqueness in the merged database.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is simply copied from one database to another during merging, then the merging process is fast and simple, but duplicate data is created and foreign key references become incorrect
Solution Approach 1:
The system performs preliminary actions by defining table uniquifiers and checking for matching rows before copying data. The row merging procedure checks if a row with the same uniquifier already exists in the target database, and only copies the row if no match is found. This preliminary checking prevents duplicates while maintaining merging efficiency.
Solution Approach 2:
The system uses feedback mechanisms by checking foreign key references and uniquifiers during the merging process. When a row is copied, the system verifies that foreign key references point to valid rows in the target database, and updates references if necessary. This feedback ensures data integrity without requiring complete re-verification of all relationships.
2Reliability
If all rows are checked for duplicates before merging, then data integrity is maintained, but the merging process becomes time-consuming
Solution Approach 1:
The system segments the checking process by focusing only on specific columns that define table uniquifiers rather than checking all columns in every row. By identifying a subset of critical columns that uniquely identify rows, the system reduces the comparison workload while maintaining accurate duplicate detection.
Solution Approach 2:
The system applies local quality by treating different columns differently during the merging process. Columns defined as part of the uniquifier receive strict matching requirements, while other columns are handled with more flexibility. This selective approach optimizes the merging process by applying rigorous checking only where necessary for data integrity.
3Reliability
If foreign key references are updated during merging, then correct relationships are maintained, but the complexity of the merging process increases
Solution Approach 1:
The system uses an intermediary approach by introducing a mapping mechanism between source and target database row identifiers. When foreign key references need to be updated, the system uses this intermediary mapping to translate references from source database IDs to target database IDs, simplifying the update process while maintaining relationship integrity.
Solution Approach 2:
The system changes parameters by dynamically adjusting foreign key reference values during the merging process. When a row is copied to the target database, the system updates the foreign key references in dependent rows to point to the new target database row identifiers. This parameter change ensures that all relationships remain valid in the merged database.
Data Source
Figure 1~2
Figure 3
Figure 4
AI summary
A computer-implemented method for merging relational databases, wherein a first database (100) to be merged has a first schema defining a plurality of tables (110, 120, 130) and a second database (200) to be merged has a second schema defining a plurality of tables (210, 220, 230), wherein the first schema and the second schema are the same, and wherein the tables (110, 120, 130, 210, 220, 230) of the databases (100, 200) contain rows (111, 112, 121, 122, 131, 132, 211, 212, 221, 222, 231, 232) of data, wherein the data in the first database (100) contains foreign keys that refer to rows of another table, the method comprising the successive steps of: defining (401) table uniquifiers; executing (402) a row merging procedure for each row of each table (110, 120, 130) of the first database (100) to define a list of new rows to be added to the second database (200); and adding (403) the rows from the list of new rows to the second database (200).