Database design how to track a change on a patient row and set in a log table

Question

I need to track any update on my patients table.

The table contains the following fields:

1. Patient ID;
2. Name;
3. address;
4. phone
5. relative name;
6. relative phone
7. smoker;
8. alcoholic.

Now I need to track if a profile of a specific patient is changed in any field, so if he was smoker 2 months ago and now not, I want to see a log that the nurse changed the profile of the patient x from smoker to non-smoker.

First Approach

Now, should I create a similar table to this but it contains a foreign key connected to the initial table and each time an edit/update is made a row is added to the second table ?

Second Approach

Or I need to track every field as a table, so I will make a table for smoking history edit, alcoholic history status table and add a row into them whenever an update happened ?

Answer 1

When I have done similar in the past, I created a changed table. For yours I would keep the patient ID as a foreign key and then list the original value for the field and the new value for the field. You do not need to keep the whole record each time it changes, just a list of the changes. I am assuming you are not required to do so by any regulatory agency, in which case check and see what they require.

I also tracked who made the change and when. You can include a field asking them to justify the change. Good info to have in case there is a question later.