Just Create a Relation Posted in Programming on 18 of May, 2021

I recently read You might as well timestamp it and today I came across an equal truism

Just create a relation.

Many a time have i seen tables of a given object where the table has a status field and a status_changed timestamp.

But guess what; knowing when a status changed, what caused it AND WHAT THE PREVIOUS STATUS WAS happens quite a lot.

So whenever I write a field called status, state or something similar I always default to making it a relation.

So instead of post table like

id title status status_changed_at body
some id some title some status timestamp body

I opt for two tables

id title body
some id some title body

and

post_id status created_at created_by
some id some status timestamp user_id

The current status is always the last entry (ordered by created_at) in that table for that given id.