Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

enter image description here

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.

share|improve this question
    
The nulls don't really bother me (they don't take up space, as far as I learned). I've found that a lot of history table design is based on how you're going to use the historical data. You might want to keep everything in the products table with a start/end date (the active record would not have an end date); or you might have a table with (id,field,orig_value,new_value) where you record fields that were changed. –  Alain Collins May 23 '13 at 5:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.