STEP1       MODERNIZATION
STEP2       ADMINISTRATION
MIGRATE-DB
RELATE-DB
EVOLVE-DB
VIEWER-DB
TEST-DB
ANONYMIZE-DB
METHODOLOGIES

Signup

Relate-DB Methodology:

Step One: Database relationships discovery

With its unique, data-centric approach, Xcase for i exploits all the information available in the meta and physical data to automate the discovery of the relationships hidden in the database.

The following outlines the algorithm used for the automatic relationship discovery:

  • Determine which tables are relational (exclude work files)
  • Qualify candidate Parent Keys and Children
  • Match PK and FK elements using metadata and common vocabulary (semantics)
  • Check candidate relationships using physical data
  • Provide a grade and detailed diagnostic to help you validate the results

Time, effort and risk of errors are reduced. Your team only has to validate the small set of relationships selected among the hundreds of thousands of possibilities.

Step Two: Normalizing tables and fixing current integrity issues

Xcase for i helps you identify automatically structural anomalies, duplicated data, integrity issues and orphan data sources.

The database normalization process is fully assisted allowing you to correct more efficiently the database:

  • Child table containing (redundant) Parent or Parent table containing Child in (fixed) columns: Split Child and Parent
  • Child table containing selective redundant data: Automate it and Eliminate it when applications are upgraded
  • Table with extensions: Join brothers
  • Referential integrity violated by the data in the database: Discover them using automatically generated SQL statements
  • Existing applications causing integrity issues in the database: Trace them using automatically generated triggers

Step Three: Alternative Relationships

A Discovered Relationship cannot be implemented “as is” in the following cases:

  • The physical characteristics of the Parent and the Child fields are not identical
    For example the Parent Key is numeric and Child is decimal or they do not share the same length.
  • The Foreign Key of the Child uses «Pseudo nulls»
    When the Parent Key is unknown instead of using a Null value a Blank or Zero are used creating de facto (for SQL) an orphan if that value does not exist in the Parent Key.
  • Referential integrity is violated momentarily
    The Child is created before the Parent
  • The Parent is filtered
    The Parent Key has a filter, for example the record must be flagged as “active” to be a Parent in the relationship.
  • Conditional Parents exist
    Depending on the value in a column of a Child the FK points to Parent A or Parent B
  • Part of the FK is implicit
    A “table of tables” is used to store various possible code values along with the code type (country, tax, region…). In the Child only the code value appears and the code type is omitted and known only by the application. This creates a situation were part of the FK is missing.

In those cases, an Alternative Relationship can be implemented between the Parent Identity and a newly created FK mapping to it in the Child. When properly set this yield to a functionally equivalent relationship producing for the same Parent the same children and for the same Child the same Parent as the original discovered relationship.

A set of automatically generated triggers updates the new FK when the old FK is modified and vice-versa. This way old applications (using the old FK) and new applications (using the new FK) can co-exist as each gets the data it needs.

Note that the triggers produce minimal overhead and are activated only when modifying data. When reading data (which happens on average 20 times more frequently) you immediately benefit from an optimized Identity based relationship.

Note also than when the old applications have been upgraded to use the new relationship, you can get read of the old FK and the triggers.

The following diagram illustrated the “Alternative Relationship” concept.