Parent-child is not the only way tables may be related. To minimize database storage requirements, attributes related to a specific entity exist on a single table. Generally tables do not redundantly store data that already exists on another table. Instead, a reference to the table is used. These tables have non-identifying relationships.
Consider a field activity step for reading a meter. The field activity step table does not store the date and time the meter was read, as this information is kept on the meter read table. The field activity step merely contains a reference to the meter read.
Special consideration must be taken to manage non-identifying relationships during the archiving or purging of production data. Most of the time, the best solution is to use non-identifying relationships as exclusion criteria, but there are exceptions where it is acceptable to allow foreign key references to archived data.
Let's take a look at this from an archive and purge perspective separately:
- Archive processes.
- When data is archived, it is moved from the production environment's database to an archive environment's database. Since the data still exists within the application, references to data moved from production to archive may still be considered valid. The system takes special measures when presenting archived foreign key references. This is considered an inter-database foreign key.
While references to foreign keys outside of the environment's database are sometimes valid, it is still good practice to minimize their occurrences. If you examine the sample archive processes, you will notice that care has been taken to reduce the number of foreign keys that reference archived data.
- Purge processes.
- Dangling foreign key references to deleted records must not be left after a purge process has been executed. After a purge, the data does not exist in any environment, so any reference to the data is invalid.