How to track history of db tables that include many-to-many mapping tables?

Question

I have seen several questions here on tracking db history, but can't seem to find one that matches our situation. We need to track the history of several tables, some of which are many-to-many linking tables. Eg say we have this schema:

EntityA
id
name

EntityB
id
name

ABLink
A_id
B_id

So, tracking changes to EntityA or EntityB seems pretty straightforward. We can keep a log table with the same columns plus a date stamp and user. But what about the links? How do we maintain the set of links that are valid for a given version of the data? We need to be able to recreate a history of the data showing changes in chronological order. So if a link added or deleted, we indicate that. Etc.

Answer 1

Create an ABLink log table (with it's own primary key if you want), that has foreign key links to the EntityA & EntityB log tables. You can add a change_type column to your log tables to indicate whether the log entry is a insert/update/delete - of course the many-to-many log table shouldn't contain any update change_type entries:

ABLink_Log       EntityA_Log     EntityB_Log
----------       -----------     -----------
id               id              id
A_Log_id         name            name
B_Log_id         change_type     change_type
change_type      date_stamp      date_stamp
date_stamp       user_id         user_id
user_id