Clear all

DV modelling scenario - orphan records in source

Posts: 149
Topic starter
Estimable Member
Joined: 2 years ago

In the source we have a table for agreements with customers and a schedule table for that agreement. The Schedule is not a payment schedule but a schedule of events for the agreement. 

Agreement -- Agreement Nr(PK), other attributes

Schedule   -- Agreement Nr (PK), Schedule Nr (PK), Termination Nr (PK), other attributes

Agreement is allowed to have only one schedule nr (=1) however there are some cases in system where multiple schedule Nr for an agreement where allowed at some time of time in past before a constraint was enforced to allow only one schedule Nr.

The logic of  Termination Nr is --

1.It is 0 for a live agreement and the same record is updated 1 when agreement ends.

2. New records may be added in the schedule table which are triggered by some customer side events and these records have same schedule Nr(=1) but a different Termination Nr(99,98...)

In the source data model there are some child tables on this "Schedule" Table and they use FK (Agreement NR, ScheduleNr and Termination Nr) to refer to the "Schedule" table. When the Agreement ends and the Termination NR is updated to 1(from 0) then it creates orphan records in these dependent tables(for Termination nr=0). The source is not implementing any constraint at DB so this is allowed. From Data Vault perspective how can i model "Schedule" as its PK can get updated?

Note - Schedule NR and Termination NR in itself doesn't have any context attributes so they are not business entities


1 Reply