6

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.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
User2910293
  • 225
  • 2
  • 13

2 Answers2

1

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 (<key>, 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.

Lennart - Slava Ukraini
  • 23,842
  • 3
  • 34
  • 72
0

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 for...as 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.

Community
  • 1
D8AGOD
  • 44
  • 3