The relational database helps to track information. For example, users, their names, logins information, various groups and the projects they are involved in. All this information are normalised into a user table, groups table and projects table.
When information about a user has been collected, the information is stored in the optional tables can be found by searching with a special key. For instance, if the login name of a user is unique, all information about that user is recorded with the login name as its key. Keys and table names play a "key" role when comparing or merging databases because it is for them to determine the identity of data.
A relational database management system uses MERGE statements to write new or overwrite existing records depending on matching conditions. Structure and tables are compared before merging. Each time software or script find differences it doesn't know what to do unless you create a rule for each situation or manage it manually. That's why It is much easier to merge the databases with the same structure, especially if you are trying to automate the process. Luckily, I have this particular case!
In an attempt to understand what kind of errors can occur during merging I’ve decomposed the database into tables and searched for various situations with unequal data structure:
Developers, data architects, and others who regularly work with databases often need to locate changes, migrate differences, or synchronise versions of database tables. They need database comparison and merging tools to satisfy all these requirements.
I’ve gathered a test group of 8 programmers who have experience with databases and asked them which tools they use for this task and how their process of usage looks like? What are the most common mistakes and how do they fix them? Also, screenshots of interfaces related to databases comparison and merging were collected and analyzed.
Based on collected data, the process of usage the utility has been recreated.
The first version of the layout is a step-by-step process that guides the user through the interface:
After the prototype was created I returned to my test group of programmers. They were asked to try merging with the prototype and give feedback. The result and time required for a user to complete the operation were measured. The results were mostly successful, but the test group was not satisfied with the user experience. They wanted to:
After some testing and fixing, the second version of the tool was created.
When you click the Start Comparison button in the Database Comparison Toolbar, contents of the selected tables are compared and the database comparison map is updated to show comparison results. If the results of automatic mapping are inaccurate or incomplete, you can also manually map comparisons by dragging connecting lines between table columns.
When different database types are compared a warning is displayed.
Detailed results window can be opened for each table comparison, and results can be filtered to display or hide your preference of equal or unequal rows, rows that only contain data in the table on the left or right, or entire columns that contain identical data.
After differences between tables are identified and fixed, content can be merged in the same way by updating individual cells or entire tables.
The second interface can be used for adding a new element. Any items can be dragged and dropped to the work area or by pressing the “plus” button and also accessing the menu.
My test group was really pleased with last version of the tool, but they still have concerns about some steps and a lot of wishes about new features. For the next releases I have decided to use a bigger programmer community to collect more feedback.