Storing history of full/partial tables I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables.

I wonder if it's efficient to store a lot of rows with NULLS on those rows that didn't change. For example this is a simplistic representation of my products table;



The products_history table has all it's field (except the FK and non-logical rows) set to NULL, whereas the normal products table has NOT NULL on all, except description.

Now what I had in mind is to push a duplicate of the actual product row and push the change into the products_history table. So let's say I have this row in products:

{600, CURRENT_TIMESTAMP, 2, 'BS001', NULL, 49.95}


and would change the price to 48.50. So I would push which values has changed, in this case the base_price and add a change-row to the history table;

{600, THE_date_created_FROM_PRODUCT, NULL, NULL, 49.95}
{600, CURRENT_TIMESTAMP, NULL, NULL, 48.50}


So here on the first modification of a Product it will get 2 rows, and each modification afterwards only one new with the modified fields). After it will update the current product row with the new base_price.

This approach works for me and is quite effective (as the history table will only be filled with product data if a product get's actual edited for the first time), but I am wondering if it this is efficient by storing all those NULL-values. Would it affect my performance after a while or would the impact not be that great?

Otherwise; I'm curious what would be good approaches to do this in MySQL, or even Django ORM-specific ways.
One way of doing this is to have a temporal table for business time (sometimes called valid time) as history. Basically what you have is a copy of your products table with additional attributes for begin_time and end_time:

CREATE TABLE PRODUCT_HISTORY
( ...
, BEGIN_TIME TIMESTAMP NOT NULL
, END_TIME TIMESTAMP
,    PRIMARY KEY (&lt;key&gt;, BEGIN_TIME) );


When you add a product you add a row to the history with BEGIN=now and END=null.

When a product changes you update the row where END=null to END=now, and add a new row as for insert.

When a product is removed update the row where END=null to END=now

All these manipulations of the history table are easily implemented via triggers.