How to track changes to a boolean column in a MySQL database?

Question

My application serves customers which are online stores. One of the tables in my DB is "Product" and it has a column "In_Stock". This is a boolean (bit(1)) column. My customers send data feeds of their product catalog and each customer has their own version of this table. I would like to track changes to this In Stock column, something to the effect of...

 11/13/2016  true
 12/26/2016  false
 01/07/2017  true

Just so that when I do some auditing, I can see for a given time period what was the state of a given product.

How best can I do this?

It seems overkill to create a separate history table and have it updated by a trigger just for one boolean column. Would a history column suffice? I can save the data there in some kind of JSON string.

Search for "audit" or "audit trail" on SO. Or just look at the right column. — DanMan, Jan 20 '17 at 21:40
"History" and "current state" are best done as two separate tables. — Rick James, Jan 20 '17 at 22:21

score 1 · Answer 1 · edited May 23 '17 at 12:08

Sorry, any workable solution will require a second table.

One such solution is Version Normal Form (vnf) which is a special case of 2nf. Consider your table containing the boolean field (assuming it is properly normalized to at least 3nf). Now you want to track the changes made to the boolean field. One way is to turn the rows into versions by adding an EffectiveDate column then, instead of updating the row, write a new row with the current date in the date field (or updating if the boolean field is unchanged).

This allows the tracking of the field, there being a new version for every time the field is changed. But there are severe disadvantages, not least of which is the fact that a row is no longer an entity, but a version of an entity. This makes is impossible to use a foreign key to this table as those want to refer to an entity.

But look carefully at the design. Before the change, you had a good, normalized table with no tracking of changes. After adding the EffectiveDate column, there has been a subtle change. All the fields except the boolean field are, as before, dependent only on the PK. The boolean field is dependent not only on the PK but the new date field as well. It is no longer is 2nf.

Normalizing the table requires moving the boolean field and the date field to a new table:

create table NewTable(
  EntityID   int  not null references OriginalTable( ID ),
  EffDate    date not null,
  TrackedCol boolean,
  constraint PK_NewTable primary key( EntityID, EffDate )
);

The first version is inserted when a new row is inserted into the original table. From then on, another version is added only when an update to the original table changes the value of the boolean field.

Here is a previous answer that includes the query to get the current and any past values of the versioned data. I've discussed this design many times here.

Also, there is a way to structure the design so the application code doesn't need to be changed. That is, the redesign will be completely transparent to existing code. The answer linked above contains another link to more documentation to show how that is done.

score 0 · Answer 2 · answered Jan 20 '17 at 13:55

0

I would do trigger thing. But don't replicate whole column - take unique column id, log timestamp and boolean value. Sometimes having good logs is priceless :)

answered Jan 20 '17 at 13:55

lapkritinis

2,664
4
19
29

score 0 · Answer 3 · answered Aug 15 '17 at 17:33

0

I've written an audit trail module for this purpose, it basically duplicates the table, add some information to each row and keep the original data table untouched except for triggers.

answered Aug 15 '17 at 17:33

Myonara

1,197
1
14
33

How to track changes to a boolean column in a MySQL database?

3 Answers3