I need to track any update on my patients table.
The table contains the following fields:
- Patient ID;
- relative name;
- relative phone
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.
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 ?
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 ?