Database Architecture

Classes

In the new system, we must be well aware what division into classes will be appropriate and what their expected ranges are. By default, there are 4 types of classes, the main user data tables, lists and codebooks, link tables and system tables. Of course the number of class types may be higher or structured differently. Together with the table name and class number are stored additional information about the class.

As in new DB as well as in the original DB we must additionally define Remote Classes. These classes then will bind with mutual definitions.

Class table contains class number (int), table name, localization flags, description, the results of integrity checking, setting of replication parameters: subscriptions/publications and triggers generation parameters.

Subjects

The most important feature of the new database is the table of all entities-subjects based on type uniqueidentifier (guid). This way of data storage allows to uniquely identifying all records at DB. From this method of identification is derived a number of methods that would not be otherwise possible. 

Table global_Subject contains system part of all rows of all user classes. This means that each user row at user tables has a fingerprint at the subject table. Id of user table row is the same as the Id at global_Subject.

Within the local insertion of row or remote subscription from remote DB, trigger creates a fingerprint into the table global_Subject. This fingerprint is maintained even after row update by trigger.

Users

The table of Users is on the border between system and user tables. For practical reasons, was included in the user tables, but contains some system data. Before migration, or by filling the table with standard users, are defined system users, which system reserves for special operations.

User Replicator is used only for replication processes of the system. If standard user is not in the original system or the user by the remote system cannot be safely assumed, under this user are subscribed all records that came from a remote server.

User IOR (Immigration Officer) is used exclusively for migration procedures. If standard user is not in the original system or the user by the remote system cannot be safely assumed, under this user are migrated all records that came from a remote server.

User Repeater is used exclusively by triggers. Repeater writes only to the first level of triggers. Triggers run ​​in special branch to that user and do not pass data changes to the next level. It provides a communication between triggers, without causing a chain reaction

User System is used exclusively by system processes. They are the data changes valid for the system only and have not to be evaluated by the user.

Definitions

Within the development of DB we created definitions for the data bridge. These definitions will be used both for migration and future replication. The right definitions are crucial for the successful implementation of a parallel system. These definitions can be written based on knowledge of both analyzes how the original DB and the new DB.

The procedure system_IO_Definition is the adjustment formula for migration and replication (M/R). This script determines how the M/R will work, where they will take data and where they will write them. What constants can be entered, as will be cast, how will establish relations and how will the whole M/R behave. The result of this script is global_Map_Bridge table, according to which works whole M/R system.

Data Integrity

If as the migration/replication remote source is not strictly relational database, with very high probability we can assume that the customer data contain integrity errors.

The target database is strictly relational and data integrity errors are therefore excluded. Experience shows that even records with impaired data integrity are valuable to the customer. These records are therefore also migrated, but cannot be updated in the new system. If integrity error is corrected in the original system, the new system receives a correction.

If the original system is relational, we have a great advantage. But almost certainly relations of original tables are not based on the type uniqueidentifier, but on the int, at best, on the bigint. The proposed system provides a comprehensive solution for data exchange between servers in case of thus solved data structures.