Just Create a Relation
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.