What is the general pattern for archive tables which need to preserve original child relationships

Say I have a table with many 1-to-many child relationships (e.g. contact numbers), and I have an equivalent archivetable which matches the colum开发者_运维问答ns of the normal table.

I'm facing the problem of archiving a row by moving the data from the normal table to the archivetable. The problem exists where the child table relationships break. How do I preserve the child data whilst being able to archive? At the moment I can only see one way which is to have nullable FK relationships from the child tables to the archive table, and then re-wire the keys on archiving.

Is this the best solution?

This is how I do it in our temporal database environment:

Ensure that your archive tables contain copies of the current latest records plus all of the previous versions.

You will also need valid_from and valid_to columns on each table to record the times at which each version of the records were valid. You can populate these using triggers when you write to the archive table..

Valid_to on the latest version of a record can be set to the maximum date available. When a newer version of a record is inserted, you'd update the valid_to of the previous version to be just before the valid_from of the new record (its not the same to avoid dupes)..

Then, when you want to see how your data looked at a given time, you query each table using SQL like:

SELECT *
FROM table a
JOIN table b ON .....
WHERE <time you're interested in> BETWEEN a.valid_from AND a.valid_to
AND <time you're interested in> BETWEEN b.valid_from AND b.valid_to