Am having relational database MySQL. The requirement is to archive the records of few tables and their related records to be completely removed from the active database and archive it to access later if required. The web application is built on Rails BTW.
Is there a way, where any insertion or update on the original database is reflected to the archived database and deletions on original database are not updated to the archived database.
That way archived database has complete data both active and archived. And the original database has only live-accessible data.
Or is there any different and efficient solution. Why because, if we were to do brute force approach, by whenever the data moved to the archived database, all the records and their dependent records need to be identified and moved to the archived database and then remove the respective records in the original database.
With the Rails framework deleting the related and dependent records is relatively easy when dependent: :destroy is configured correctly.
Counterexample "I"
Insertions and updates are reflected, deletions are not. But an evil colleague might just replace any content with dummy content before deleting a record, so only the relations between the records will remain, but everything else will be gone. This may make the "no-delete mirroring approach" useless (except you can deduct valuable information from the link graph itself).
If the archived database also has all technical properties of the original one, this is counterexample "II":
Imagine a table that has UNIQUE groups defined for some of the columns: UNIQUE (Name, Surname, Birthplace, Birthdate)
Now someone deletes Jane Doe, London, 2000-01-01. The shadow table will preserve this record, though.
After a week, you note that this was a mistake made by some colleague. You want to re-create Jane, but since her data is still present in the shadow table, this will be refused and the transaction won't commit.