Never Delete Data
Abstract
AS it says: never delete data.
How?
Rather than delete a row or record, update it to have a flag "deleted" set to true. THen write your code to heed this flag as appropriate.
Why?
Because you'll thank me later. Not everyone wants to see "deleted" records. But when you encounter that case where you do, you'll be glad you have it.
Level 2: Updates
Level 2 is never remove a record, even with an update. This is done by allowing changes only by deleting/retiring one record and replacing it with another.
Level 3: Audit log
Level 3 is adding a quick message about who or what caused the insert, update or delete.
Too much data!
When you actually have constraints on how much data you can have, then you need to partition. I recommend partitioning on date and currency. That is, keep the most recent, most current records.
However, do not make the 2nd tier data unavailable. Make your app know how to talk to the backup.
