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.