history preserving data models

Start with a super simple data model:

CREATE TABLE kv (
  id BIGINT(22) NOT NULL AUTO_INCREMENT,
  k VARCHAR(255) NOT NULL,
  v LONGBLOB NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY u_k (`k`)
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Suppose we want to audit “changes” to this data model.

Approach 1: kv_log

add data model like

CREATE TABLE `kv_log` (
  id BIGINT(22) NOT NULL AUTO_INCREMENT,
  changed_at TIMESTAMP NOT NULL,
  k VARCHAR(255) NOT NULL,
  old_v LONGBLOB NOT NULL,
  new_v LONGBLOB NOT NULL,
  
)

current value query: unchanged

log query:

SELECT *
FROM kv_log
WHERE k = :key;

• PRO: straightforward
• PRO: separation of concerns
• CON: might forget to write kv_log record when updating records via a ROLL plan, or even

Approach 2: kv_events

replace kv with

CREATE TABLE `kv_events` (
  id BIGINT(22) NOT NULL AUTO_INCREMENT,
  update_type ENUM("INSERT", "UPDATE", "DELETE") NOT NULL,
  changed_at TIMESTAMP NOT NULL,
  k VARCHAR(255) NOT NULL,
  v LONGBLOB NOT NULL,
  PRIMARY KEY (`id`),
  KEY k__key__created_at (`k`, `created_at`)
)

current value query (NOTE: this is a bit incorrect as it stands, because the most recent record might be a DELETE…)

SELECT *
FROM kv_events
WHERE k = :key
ORDER BY changed_at DESC
LIMIT 1;

log query:

SELECT *
FROM kv_events
WHERE k = :key;

Approach 3: kv2

replace kv with:

CREATE TABLE kv2 (
  id BIGINT(22) NOT NULL AUTO_INCREMENT,
  effective_at TIMESTAMP NOT NULL,
  effective_until TIMESTAMP,
  active TINYINT(1),
  k VARCHAR(255) NOT NULL,
  v LONGBLOB NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_k` (`k`, `active`)
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

current value query:

SELECT *
FROM kv2
WHERE active = 1
AND k = :key;

log query:

SELECT *
FROM kv2
WHERE k = :key;