Home

• Architecture
  • Glossary
  • Variants
    • Database foreign key constraints
      • Pros
      • Cons
    • Propagation of versions to children
      • Pros
      • Cons
    • Propagation of versions to parent
      • Pros
      • Cons
    • Update the child to the new parent version
      • Pros
      • Cons
    • Surrogate business key
      • Pros
      • Cons
  • Solution

Architecture

Implementation was started with the idea of creating cascading row triggers to handle row by row. The idea is that new records are thrown at the table as inserts or deletes. Updates will inhibited.

Glossary

Version: A version of the payload specifics for one business key of an entity resulting in a record in the entity's table.

Variants

Can be combined with eachother.

Database foreign key constraints

Pros

1. Relation is easily visible also in models
2. Query optimiser could use the information to get a better execution plan

Cons

1. It is no strict relation. Assume there are new versions of a child but the parent does not change at the same time, you can find only one of the children directly coming from the parent. This holds true the other way round too.

Propagation of versions to children

Create for each parent version a child version - cascading and branching.

Pros

1. Remove one part of the first con of the database foreign key.

Cons

1. Introduction of artificial records bloating the table.
2. Leads - in combination with the next variant - to denormalisation even though technically in different tables.
3. Propagation leads to performance penalties.

Propagation of versions to parent

Create for each child version a parent version - cascading.

Pros

1. Remove the other part of the first con of the database foreign key.

Cons

1. Introduction of artificial records bloating the table.
2. Leads - in combination with the previous variant - to denormalisation even though technically in different tables.
3. Propagation leads to performance penalties though to lesser extents than previous variant.

Update the child to the new parent version

In case there is a new parent version, and the making of the new version does not get propagated to the children making there new versions too, one can update the children of the original parent to point to the new parent.

Pros

1. The "current" state gets reflected quite perfectly.

Cons

1. Suffering from performance penalty but to a lesser extent than the creation of new versions.
2. Infringement of the DWH rule not to alter any record but on technical level.

Surrogate business key

The surrogate business key is an artificial key to embrace versions of one business key into one artificial key and optionally to merge more than one business key. E. g. two systems have their own primary key for a person but both carry the social security number. One can integrate those two systems by the social security number and create an artificial surrogate for the primary keys and the social security number - the surrogate business key.

Pros

1. Possibly easier join conditions than on the original keys.
2. For hierarchical joins it is possible to find the desired versions when filtering (joining) on business and/or technical validity.

Cons

1. Probably no database foreign key constraint on these columns possible.
2. Additional effort with the data integration.
3. For hierarchical joins without filtering on on business and/or technical validity one gets all the versions.

Solution

In short, there is no one fits all solution. We start off with:

• Mandatory database foreign key to be able to get the relation for the database catalog.

The following shall be implemented as options probably in that order.

1. Update child to new parent version
2. Surrogate business key (don't yet think there is a useful generalisation for this with respect to historisation)
3. Propagate versions to children which obviates 1.
4. Propagate versions to parents.