2

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 ?

  • 2
    You may find my answer to question a and my answer to question b of interest. Both are related to the temporal aspect of the relevant databases, and seem very similar to the first approach you describe. – MDCCL Jul 7 '17 at 14:06
  • 1
    Okay sir I will see them now – alim1990 Jul 7 '17 at 17:04
  • So the 2 approach are correct. By the way, can you explain more about the IDEF1X – alim1990 Jul 8 '17 at 6:22
  • I appreciate the IDEF1X standard because it assists considerably in capturing the meaning of the business domain of interest if one wants to implement a relational database on a SQL platform. Basically, (1) each entity type becomes a table, (2) each attribute becomes a column, and (3) the primary, alternate and foreign keys become the corresponding constraints. Importantly, it allows describing the entity type interrelationships in the models themselves through verb phrases. All of the above enhances communication (a) among technical people and (b) among technical and non-technical people. – MDCCL Jul 10 '17 at 16:55
3

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.

|improve this answer|||||
  • So i will duplicate the save table but with a foreign key in the second table is patient_id and then add 2 rows, the first is the original and the second is the new one ? – alim1990 Jul 8 '17 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.