Balancing history with performance This is a general design question, not related to a specific DBMS.

I will use a basic employee record to illustrate.  I'm not worrying about the specific syntax, I am asking for help with the approach.

CREATE TABLE employee(
id SERIAL,
name TEXT,
manager_id INT4,
modified TIMESTAMP DEFAULT NOW(),
modified_by INT4
);


Some ways to track changes to the manager_id in the employee table -


A database trigger to an audit table.  Requires no additional application code, but must be kept current with the source table to be of value
An audit log file that saves only the changed values in a JSON string. 
Move the manager_id into a separate table and add an entry for every change.  The separate table would have an id, employee_id, manager_id and perhaps a current flag.  Retrieval would mean the whole table would have to be searched for the most current manager.


I can't use an ORM, which is what I would really like to do.

My question:

Is it better to keep a distinct, single record of the current data or sift through a table with history of the data's state each time?
If you want to know who was an employee's manager back in 2015, you need a separate table for that; employee_id, employee_id_of_manager, start_date, end_date.

The relationship may be more complex; what if a manager takes paternity leave?  Or job-shares, so that two or more people are sitting at that desk in rotation?

Having said that, a separate table of relationship history AND a column of employee_id_of_manager in the employee table may be a useful design.  For the job share or paternity leave, it may be better that the "manager_id" doesn't represent one person, but, for instance, the id is "John" but the database also knows that "John" is on leave and "Andy" is the replacement for "John".  Or "Peter and Paul" is the replacement, a composite person whose distinct parts are "Peter" and "Paul".

If we forget about employees and managers, and make the question about generally tracking changes to data, then it does become just a generic question about preserving an audit trail and history of data.  Of course, in a Microsoft SQL database for instance, when you update the manager_id then any previous value is destroyed and can't be recovered (except by the FBI or CIA who have been tapping all your data anyway ever since "Operation Paperclip").  The data is gone, unless you have in place any of the systems you refer to, or you have enabled the other feature in SQL Server 2016 which does the same thing but I've forgotten what it's called.  "Backup"?  No, it wasn't that, but that works too......

You could run an overnight job which looks for data which has changed, and copies it to a history table then - actually, that sounds like "Replication".

Some data you'd want to protect this way, some you wouldn't, so, you could split the table into two tables, one of which gets the audit treatment.  And gets the "Everlasting Transaction Log" or "Infinite Undos" facility applied to it.

I would prefer to design the historic record keeping specifically, but, if your SQL Server has an automatic tool for it (and we've covered several, with less or more "automatic" involved), then you may as well use that... maybe.

Another condition though that may apply to holding people's personal data - you may be under a legal obligation to -not- keep data that is old and no longer relevant.  To destroy data that, in fact, you have no good reason to keep.  This won't apply if you ARE the FBI or the CIA, of course.

Otherwise, this is awkward if your data retention plan consists of a 50 year cellar of backup tapes, and the law says that you need Giant Magnetic Bridget to pay you a visit.  (Please look up Giant Magnetic Bridget on Google.  She's an interesting subject that I learned about recently.)

So... maybe plan for necessary selective deleting of old records.

You could encrypt, and throw away the key to make certain data unusable - but then someone cracks the encryption, inevitably.  So the data can be read again. 
 There is another interesting subject called a "One Time Pad", but the authentic thing is expensive and impractical - although simple: you use a file of random bytes the same size as your database, and every data byte that you store is first transformed by "Exclusive Or" (xor) operation with the corresponding random byte.  To read the data out, perform the "Exclusive Or" again.  To "destroy" the stored data, destroy the random data bytes.  That code can't be broken... but your system may have other points of leakage in it.  But in order to "delete" your copy of the data and all its backups, it'll do.

Although, in Microsoft SQL Server, the bytes in the file that represent one employee's data aren't fixed in place - so you'd need to invent a way to do that.