Other SQL History tables

[PatrikCavina]

Hi to all,
I'm building database structure in SQL, i want hold history of change in record in tables that have many to many relationship.
Tables are A, B and AB_Map where AB_Map table is the link table between A and B.
Considering table B immutable (records inside table B never change so table B doesn't need an history)
The scanario is this:

Table A
ID_a (PK)
a_field1
a_field2
a_field3

Table AB_Map
ID_a (PK, FK1)
ID_b (PK, FK2)
quantity

Table B (Records never update)
ID_b (PK)
b_field1
b_field2

So i want hold history of Table A and Table AB_Map.

Only way that i thought is this:

Create Table A_History and Table AB_Map_History:

Table A_History
ID_a_history (PK)
ID_a (FK)
a_field1
a_field2
a_field3

Table AB_Map_History
ID_a_history(PK, FK1)
ID_b(PK, FK2)
quantity

For each changes in table A the previous record it will insert in A_History, and linked records in Table AB_Map will insert in AB_Map_History.
This way will create record in Table_Map_History also if there isn't any change between the relation with A to B.
I think that is the easier way to do this, but i don't like to consume memory if doesn't necessary.

So I would like to know if there are other ways to handle this situation and what do you do in these situations.

Now i use this trigger on A table to resolve my situation:

CREATE DEFINER = `root`@`localhost` TRIGGER `SaveHistory` BEFORE UPDATE
ON `A` FOR EACH ROW
BEGIN
    INSERT INTO A_History(
        ID_a_history,
        ID_a,
        a_field1,
        a_field2,
        a_field3
    )
VALUES(
    null,
    OLD.ID_a,
    OLD.a_field1,
    OLD.a_field2,
    OLD.a_field3
);

INSERT INTO AB_Map_History(
    AB_Map_History.ID_a_history,
    AB_Map_History.ID_b,
    AB_Map_History.qnt_kt
)
SELECT
    LAST_INSERT_ID(), AB_Map.ID_b, AB_Map.qnt_kt
FROM
    AB_Map
WHERE
    AB_Map.ID_a = OLD.ID_a;
END

Thanks in advance

 

[Jorge M A]

The only different thing I implemented in a large production system is that the historical tables include a field on the type of operation: "I" for insertion, "U" for update and "D" for deletion, as well as the timestamp, the user (if you have it) and the primary key is an autoincremental to get the changes chronologically.

 

[PatrikCavina]

Thanks for suggest, i can proceed in this way.
I've update my post, adding trigger function on table A if someone need it