Your CMS is not the source of truth!!!!!!
A content model earns its keep by what it refuses to store, not by what it can hold. Notes on referential integrity, and the guarantees you give up the moment a reference becomes a string in a text column.
TL;DR
- A reference inside a string is invisible to the database.
- You will reimplement population, validation and delete protection by hand.
- That is a fine trade, but make it deliberately.
Every content model has a moment where it stops describing the world and starts describing the editor that produced it. Usually nobody notices until something needs to change.
Testing an Image
The comfortable lie
A CMS presents content as documents. Underneath, what it actually stores is a graph: posts point at authors, authors point at avatars, posts point at categories. The document view is a convenience over that graph.
The moment you store a reference inside a text field rather than as a column, you take that edge out of the graph and hide it in a string. Everything the database used to do for you becomes your job.
What you give up
- Population. Nothing resolves the reference at read time; you write that.
- Existence. Nothing stops a reference pointing at a row that was deleted last Tuesday.
- Reverse lookup. Which posts use this image? becomes a table scan instead of an index hit.
- Delete protection. Nothing knows the row is still in use, so nothing refuses.
A concrete case
Suppose post bodies are stored as text, and images are referenced by id inside that text:
const MARKDOWN_IMAGE = /!\[([^\]]*)\]\(([^)\s]+)\)/g
const IMAGE_TOKEN = /^image:(\d+)$/
export function scanBody(body: string): number[] {
const ids: number[] = []
for (const [, , url] of body.matchAll(MARKDOWN_IMAGE)) {
const token = IMAGE_TOKEN.exec(url)
if (token) ids.push(Number(token[1]))
}
return ids
}That function is small and it works. It is also the first of four things you now own, because every guarantee in the list above has to be rebuilt on top of it.
The tell is not that the code is bad. The tell is that you are writing code whose only job is to recover something the database was already willing to give you.
Counting the cost honestly
| Guarantee | Column reference | Reference inside text |
|---|---|---|
| Populated on read | free | hand-written hook |
| Existence checked | free | hand-written validation |
| Reverse lookup | indexed query | full scan |
| Delete protection | one guard | one guard, over a scan |
Three of the four rows are a straight loss. The fourth is a wash.
So why do it anyway
Because the string is portable and the graph is not. Text survives the CMS that produced it. It diffs, it greps, it can be hand-edited at three in the morning without booting an admin panel, and it does not encode a particular editor’s node schema into your archive.
That is a real benefit and it is worth real money. The mistake is not choosing it. The mistake is choosing it without noticing that you just signed up to write four things by hand, and then being surprised, six months later, that the system feels like it is fighting you.
What to actually do
- Decide which references genuinely need to be portable.
- For everything else, use a column and let the database do its job.
- Where you keep the string, write the four guarantees up front rather than discovering them one incident at a time.
The worst outcome is the hybrid you drift into by accident: half the edges in columns, half in strings, and no memory of which decision was deliberate.
Related Posts
