best way to keep track of myql changes

Question

I'm using MySQL workbench's EER tool to make the database design for an application I'm developing. it is very important to track data changes on the DB, so i was following this post which makes a lot of sense the accepted answer. the problem im having is that i am not good understanding the primary keys so im a little confused on how this should work, since every time a change is made there will be another row inserted with the same Id, therefore the DB wouldn't allow it am I right? And i need to use a primary key because of foreign keys.

Answer 1

My normal "pattern" in (very)pseudo code:

• Table A: a_id (PK), a_stuff
• Table A_history: a_history_id (PK), a_id(FK referencing A.a_id), valid_from, valid_to, a_stuff

Triggers on A:

• On insert: insert values into A_history with valid_from = now, and valid_to = null.
• On update: set valid_to = now for last history record of a_id; and do the same insert from the "on insert" trigger with the updated values for the row.
• On delete: set valid_to = now for last history record of a_id.

In this scenario, you'd query history with "x >= from and x < to" (not BETWEEN as the a previous record's "from" value should match the next's to "value").