Notifications
Clear all

Zero Keys with links from XREF Tables


Posts: 48
Topic starter
(@andrewrobinson)
Eminent Member
Joined: 2 years ago

I'm trying to understand the correct logic here for implementing Zero KEYs when we're dealing with an XREF table vs. a parent FK column on a child table.   I don't think the Zero KEY records will develop naturally but I'm not sure if I'm missing something (trying to close gaps).

Here is the use case:

  • We have a Parent A
  • We have a Parent B
  • Parent A can be related to one to many Parent Bs over time (some Parent As will be associated to Parent Bs but others never will be)
  • Parent A is implemented as a source table
  • Parent B is implemented as a source table
  • The relationship between Parent A and Parent B is implemented as a separate XREF table (the relationship carries the history as it has an effective date).  Basically Parent A can be related to many Parent Bs over time - so that repeating element is implemented as a separate table.

Therefore if Parent A and Parent B are not associated together then there will be no records on the XREF table - which would translate therefore to no records on the link table.  This leads to then needing to perform a left join when bringing this information together - as there simply won't be a record in the link table when the source didn't relate these together.

Should we add a process to create a Zero Key on Parent B (as again nothing would naturally create this as there is no NULL FK) and insert additional records into the link table to associate Parent A with Zero KEY Parent B records?  Or do we accept the left join being necessary in this case?

This is a different situation than when we have the NULL FK in the source - as that drives the creation of Zero KEYs and Link records to the Zero KEY.  What's challenging is that the "NULL" is essentially a missing record in the source.

 

Reply
Topic Tags
2 Replies