Data Vault Patterns

No questions yet.

Data Vault Satellites

How to build an End-Dating Satellite?

Purpose The End-Dating Satellite is a variant of a Satellite. In contrast to the Satellite, the End-Dating Satellite contains no…
Purpose

The End-Dating Satellite is a variant of a Satellite. In contrast to the Satellite, the End-Dating Satellite contains no context attributes for relationship in the associated link.

An End-Dating Satellite is intended solely for determining the validity of a relation between two (or more) hubs. With this the data modeler can design an m:n (cardinality) relation from an n:1 or 1:1 relation (1). Without changing the pattern of the link itself.

Motivation

The special feature of the End-Dating Satellite is that it is modeled exclusively on a link. The historicization of the End-Dating Satellite is initiated by a so-called Driving-Key. The Driving-Key identifies the column that must be unique at a certain point in time.

Alias
  • Effectivity Satellite

Applicability

Cardinality 1:n (2)

One entity on one side of the relationship (master) is opposed by no entity, one or more entities on the other side (detail).

Art: A (certain) work of artwork can (at the same time) only be exhibited in one museum (as original); in a certain museum, on the other hand, several works of artwork are presented. → Museum 1:n Artwork(s)

Entity artwork is the Driving-Key.

Family: A child has exactly one biological mother, this mother can have several children. → Mother 1:n Child(s)

Entity child is the Driving-Key.

Cardinality 1:1

In a 1:1 relationship, exactly one entity is assigned to exactly one other entity.

Marriage: A spouse is (usually - i.e. apart from special polygamous cases) married to exactly one spouse.

Entity spouse (2x) is the Driving-Key.

Car license plate: Until July 2012, a registered vehicle in Germany had exactly one license plate and each license plate belonged to exactly one vehicle (see also interchangeable license plates).

Entity registered vehicle and license plate is the Driving-Key.

Content

LoadPatternId: 6

In the load pattern, the 1:1 relationship is realized by using a Driving-Key-Stereotype in the associated link.

The historicization of the End-Dating Satellite is initiated by a so-called Driving-Key. Since the Driving-Key identifies the column that must be unique at a certain point in time it results in the following historicization szenarios:

  • Relation is changing:
    • Non-Driving-Key is changing and NOT NULL
    • Old relation is edndated in End-Dating Satellite.
    • New relation is valid in End-Dating Satellite.
  • Relation is not longer existing:
    • Non-Driving-Key is changing and NULL
    • Old relation is edndated in End-Dating Satellite, aka logical deleted.
Sample

On the basis of the data model shown here, an exemplary design for a End-Dating Satellite shall be shown.

Stereotype

To design an End-Dating Satellite use the Stereotype CL - Link-Satellite EndDating.
To design a Driving-Key in the associated link use the Column-Stereotype CL - Driving Key.

Mandatory columns

To design an End-Dating Satellite the following columns are mandatory:

  • Surrogate Key
  • Load Date
  • Load End Date

 Satellite End Dating Satellite One To ManySatellite End Dating Satellite One To Many

 

Satellite End Dating Satellite One To OneSatellite End Dating Satellite One To One

 

Stage table

To design stage tables for an End-Dating Satellite the following keys are recommended to ensure a high data quality:

  • One To Many: A Primary Key or one Alternate Key (or ensure uniqueness of column in views on top of the table)
  • One To One: A Primary Key and one Alternate Key or two Alternate Keys (or ensure uniqueness of column in views on top of the table)

Satellite End Dating Satellite One To ManySatellite End Dating Satellite One To Many

Mapping

To design the mapping for an End-Dating Satellite the following columns are mandatory to map:

Satellite End Dating Satellite One To Many Sample MappingSatellite End Dating Satellite One To Many Sample Mapping

Apart from the columns shown above, no other columns need to be mapped.

References

1) For more details about cardinality in data modeling have a look at e.g. link.
2) Examples from link.

Posted 2 years agoby admin.tedamoh