Sign up ×
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}

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

1 Answer 1

To answer your storing NULL question: Storing NULL Values / Performance

When setting up a historical model like such, you really need to pay attention to what the historical data will be used in, will I need this data for historical reporting, trivial lookups, metrics / analysis, corporate data compliance (the list goes on)?

If there is no need for the historical data based off of the above mentioned, then simply do your CRUD up against the base table and add a "modified_at" | "modified_by" etc. field to represent the most recent change and what changed it, i.e. "System", "UserId" etc.

If a historical model is imperative or a business requirement, then extrapolate on the historical table by adding the needed fields that will keep your auditing and serialization at bay, i.e. "modified_at" etc... In addition use MySQL Triggers MySQL Triggers & Examples

The triggers will allow you to manipulate what data you need / want in history by utilizing snippets of conditionals and the NEW.* / OLD.* syntax. By doing so you can limit what NULL values you store to the data page based on your individual use cases. Ensure to read up on BEFORE / AFTER trigger actions for data retention reasons.

share|improve this answer

Your Answer


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

Not the answer you're looking for? Browse other questions tagged or ask your own question.