The forbidden semicolon: when ticket comments lived in a single field

An IT guy built a company CRM, all by himself. Like almost all software born to solve one single problem, it had a predictable fate: it grew. Year after year it got bigger and bigger, and every new need added a new module.

The ticket system "for yesterday"

In 2022 an internal ticket system was needed too. It was needed right away, naturally.

When you are in a hurry, the question "how do I store the comments of a ticket?" has a very fast answer: in a field. A single text field. And how do you separate one comment from the next? With a character nobody ever uses. The semicolon.

Restarted the service;Still not working;OK, it works now

Three comments, one field, not even one extra table. The system was born like that, straight in the database, and for a while it worked beautifully. The way all temporary solutions work: as long as nobody looks at them closely.

The forbidden character

There was only one problem, and our IT guy solved it the way these problems are solved: by pretending it didn't exist. If anyone typed a ; in a comment, the server removed it. Without warning. The separator was safe; the comments, a bit less so.

Then, one day, a colleague (a programmer too) asked him:

"How come I can't type a semicolon in a comment?"

It must be said that to a programmer the semicolon is not punctuation: it is almost a vowel. Preventing them from typing it is like forbidding a singer the letter a.

Our IT guy did the only thing he could: he answered with the truth. And the truth was embarrassing in its simplicity: the semicolon was not a forbidden character, it was the architecture.

A year later

In 2023 the time came to build the new ticket system, the one done properly, with every comment in its place.

Then came the migration. Every ticket of the old system carried its comments glued together in a single string, and to put them back in order there was only one tool:

foreach ($oldTickets as $t) {
    foreach (explode(';', $t['comments']) as $comment) {
        // ...and this is where the fun began
    }
}

It took really a lot of explode() calls. One thing must be granted to the old system, though: the separator was reliable, because no comment could contain it. It was the only clean piece of data in the whole database, and to get it a chunk of what the users had written had been thrown away.

Today everything works correctly. Each comment has a row of its own.

What this story teaches us

• One comment, one row. Data that repeats (comments, attachments, messages) deserves a table of its own, not a field with a separator in the middle.
• A character used as a separator is a forbidden character. Sooner or later someone, a colleague, a customer or your future self, will want to type it.
• Stripping inconvenient characters is not validation, it is data loss. What reaches the database is no longer what the user wrote.
• If the honest answer to "why?" embarrasses you, the design needs rethinking. Better to notice when a colleague asks than when it is time to migrate.
• Technical debt always gets paid. The only choice you have is when, and with how many explode() calls.

Today you can write anything in a comment; even this.