Search code examples
phpmysqlinnodb

Storing changes/versions of a table row in MySQL?

I'm wanting to be able to see and also query with PHP changes to rows of a table so I can see the history.

For example if someone changes a title for a product name, I can see the different versions and when they were changed.

What's the best way to go about this, does MySQL have a particular feature to handle this or is it a case of creating a table for historic changes with fields for date changed then before and after of a particular field?

Solution

  • In a data warehousing There are generally 2 Methods:

    1. create a slow moving Dimension
    2. create an Audit Table as suggested above

    1) Slow Moving or Slow Changing Dimension

    These are great for tracking a number of changes at a given time - 1 or more values may change at the same time - this stores all the changes and unchanged data in a new row - identified by a timestamp.

    No so good at knowing what specific data has changed. Good at showing a current position of the Stock etc.

    For Example, If your product table currently consisted of:

    PRODUCT_ID
PRODUCT_NAME
PRODUCT_COST

    you would add additional fields to track changes, for example

    VALID_FROM
VALID_TO

    When a Product is added the VALID_FROM will be the datetime it is added and the VALID_TO a date such as 31DEC3000... Note this record is always valid until it is changed.

    When a PRODUCT is updated: Change the old product record that had a VALID_TO of 31DEC3000 to the current DATETIME and add a new record with VALID_FROM being the current datetime... VALID_TO would be the future datetime you have decided on.

    Anytime you query your data you need to know when you want the data to be 'As Of' so you can query with something like :

    where AS_OF_DATETIME is between (VALID_FROM and VALID_TO)

    2) Audit Table Method

    An Audit table stores individual variable changes and would usually consist of a standard set of columns.

    This is great at knowing what specific data has changed. Not so good at showing a current position of the Stock etc.

    TABLE    /* Table the Changes were made */
ID       /* ID or key to the data in the table */
VARIABLE /* The name of the variable that changed */
DATETIME /* Date and Time of the Change */
BEFORE   /* The Value of the variable before the Change */
AFTER    /* The Value of the Variable after the change */
USER     /* Who made the change */
PROCESS  /* By what process the change was made */