⟵ Back to main page

 

Database manager for merging and comparing

Design Utilities that merge 2 relational databases, saves this process in a temporary file for later editing and exporting the new database to the desired format.

The problem to think and to solve

  • What is relational Database?
  • Why is the special tool needed for merging?
  • Who will use this tool?
  • What feature makes this tool comfortable for users and unique on the market?
  • How is the process of merging databases going on?
  • Where is the point of success in this process?

Research

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:

  • The one field in the database A matches the same field in the database B;
  • The one field in the database A does not match the same field in the database B;
  • The one field does not exist in the database A but does exist in the database B;
  • The one field does not exist in the database B but does exist in the database A.

Audience

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.

User flow

Based on collected data, the process of usage the utility has been recreated.

First attempt

The first version of the layout is a step-by-step process that guides the user through the interface:

  • Step one: Load 2 databases and specify where to save the merged one. The databases can be added as files or as a link to a remote server.
  • Step two: Inspect the structure of the loaded databases and select the rules and settings for common cases before merging.
  • Step three: Edit remaining conflicts manually (If the general rules are not chosen)
  • Step four: save and export the database.

Testing

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:

  • Add a number of conflicts in each table
  • Be allowed to apply rules for automatic merging to individual tables and to the entire database.
  • Add a preview of the table with the highlighted conflicts.
  • See an open record as a column not as a row.
  • Remove the parameter names from inside the table because they are useless and not interesting.
  • Consider that the utility will not be able to load photos from database dumps

After some testing and fixing, the second version of the tool was created.

Start

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.

Creating rules

When different database types are compared a warning is displayed.

Table preview

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.

Fixing one-to-many cases

After differences between tables are identified and fixed, content can be merged in the same way by updating individual cells or entire tables.

Fixing one-to-one cases

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.

Conclusion

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.


 

Also you can be interested in

  • Floor plan drawing software
  • Designing SMM&A flow‑based interface
  • Gambling mobile application interface.