3 ms·
Which is why I don't add that extra deleted field. Rather duplicate all the tables into a new database called "archive" and then insert there before deleting fr
by habibur 4y ago
Which is why I don't add that extra deleted field. Rather duplicate all the tables into a new database called "archive" and then insert there before deleting from main.
That works for updates too, by preserving the old data and showing you a time machine like backlog. But the archive database gets too large over time and you need to purge it periodically. You can create some delete triggers for automating this "save before delete" behavior.
- tehbeard 4y agoHow do you account for maintaining integrity in the archive? E.g. you have 3 users sign up with the same email (a unique field) one after the other with deletions in-between each sign-up?
- habibur 4y agoNo PK, FK or Unique constrains on the archive. Rather use simple index to speed up queries.
- justin_oaks 4y agoI'm not who you asked the question of, but I do sometimes make use of archive/deleted/history tables. I'll refer to them as history tables from here on out. In short, I leave data integrity to the original table and drop it for the history table. The history table isn't identical to the original table. It has it's own primary keys that are separate from the original table. It doesn't include the original table's unique constraints or foreign key constraints. It also generally has a timestamp to know when the record was put there.