best way to keep track of myql changes

问题:

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.

回答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").