3

This question is about possible approaches for time stamping every column of every row in a table.

I have a table with a small number of rows (fewer than 200 and not likely to grow much larger). Each row refers to an individual entity and each column corresponds to piece of data about that entity. The columns will be updated whenever new information becomes available, and I need to record when that happens.

For example, think in terms of a contacts table:

CREATE TABLE `contacts` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100),
    `address` VARCHAR(100),
    `phone_number` VARCHAR(100),
    `email_address` VARCHAR(100),
    `updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

If the phone number is updated this month, we might be inclined to believe that the `email_address` was updated as well, even though it may be severely outdated.

I can think of three approaches:

1. Create an additional timestamp column for each column:

CREATE TABLE `contacts` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100),
    `name_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `address` VARCHAR(100),
    `address_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `phone_number` VARCHAR(100),
    `phone_number_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `email_address` VARCHAR(100),
    `email_address_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

2. Or, I could created a secondary table that contains the same information:

CREATE TABLE `contacts_updated` (
    `id` INT,
    `name` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `address` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `phone_number` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `email_address` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_contacts_updated_contacts_id`
        FOREIGN KEY (id)
        REFERENCES contacts (id));

In which case, is there an easy way to duplicate a table and automatically change the column types to timestamps? And use triggers to update the secondary table?

3. Or, I could create an Entity-Attribute-Value table:

CREATE TABLE `contacts_updated` (
    `id` INT,
    `column_name` VARCHAR(15),
    `updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

...but this seems like it would be more difficult to query the information.

Are there other options that would be better? And I also assume that the answer will be different for a small dataset vs. a large one, so I'd love to know your opinion on both cases.

1
  • Keep structure #1 and make your code update the applicable timestamp on change. EAV - too complicated especially for a fixed number of simple records
    – danblack
    Commented Jul 18, 2020 at 0:45

1 Answer 1

1

COMMENTARY

I love this question because I answered another question like it 7 years ago

You actually wrote the SQL I had suggested. I am impressed.

YOUR ACTUAL QUESTION

IMHO I would go with Suggestion #3 because its implementation is as normalized as you are going to get. The retrieval of the deltas for any column would consume the least amount of memory in the InnoDB Buffer Pool, memory within the DB Connection, and the smallest footprint on disk.

The other suggestions would have so much redundant data that retrieving the delta info would most likely push your working dataset out of the InnoDB Buffer Pool frequently.

CAPTAIN'S LOG : SUPPLEMENTAL

Surprisingly, there is an alternative.

MariaDB has implemented all the above suggestions into a table metadata framework called a Temporal Table. What you are going to implement falls under the category "System-Versioned", which tracks the Change History of all table rows. The best part about "System-Versioned Temporal Tables" is that you do not need to explicitly create and track timestamps. That's done for you.

The only drawbacks I can think of are the following:

  1. Small Learning Curve in Understanding MariaDB's SQL Dialect for Temporal Tables.
  2. The timestamps are for the row not the column. This means a column may change or not from one iteration of a row's point-in-time to the next. You may need write some fancy SQL GROUP BY or DISTINCT aggregation to go through each row and stare at the column you wish to track or audit.
2
  • Good information, sir! So a less generic part of my requirements is to give each column a "temperature" color so the end user will know how current each data point is at a glance. So to do this, I would need that date information for every column every time I query a row. What would that query look like? All I can think of is one that has a join for every column in the first table. Would this change your approach? And thank you for the encouragement, too! It's nice to know my thinker is working.
    – pbarney
    Commented Jul 18, 2020 at 14:00
  • I guess the current row's timestamp would suffice as the latest for all columns. Only if you need the audit trail for a column, then you would use the EAV list on the row and collect the column values and go from there. Commented Jul 18, 2020 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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