The Most Valuable Column Nobody Thinks About

This week I found myself looking at a database table after noticing some interesting logs in app insights, and asking what should have been a simple question:

How old is this record?

Checking the table structure, I immediately cringed. The reason? There wasn't a timestamp column.

No CreatedDate, no CreatedUtc, no TS, no audit information; just the data.

Don't get me wrong, it's not the first time I've run into this, and I suspect it won't be the last.

You never miss it until you need it

The interesting thing about timestamp columns is that they rarely feel important when a table is first being designed.

When you're building a feature, you're focused on ensuring that the structure will capture the required data to make that feature work. Names, IDs, statuses, relationships, configuration values - whatever the business requirement happens to be, but it's such an important column.

A record without a timestamp is missing part of the story, and you're left trying to reconstruct history from clues like the Id value.

Without it, you're missing key information when digging into issues - has this always been a problematic record that's just never tided up or is it a new one? When did this get inserted?

Let alone trying to perform some sorting or even reporting, based on dates.

Future you will definitely thank present you

One thing I always remember is forgetting to add a timestamp column to a table within an app I had created. The requirement to update the data escapes me given it was so long ago, but I remember kicking myself for not having a timestamp column to track when the record was inserted - I remember my lead at the time, explaining to me about a year prior to this, that there will be a time when I regret not adding it, as he had learnt by experience when he started long ago - low and behold, it was like a torch was passed, and I certainly regretted not having that timestamp column now!

Safe to say, I added that column in the next app update and made sure that I engrained this in my brain, as a core column to be added to any table I create going forward.

Interestingly, I saw this play out recently with a developer I was mentoring. I'd shared a story about running into problems because a table didn't have a timestamp column, but when they later built a similar feature, they didn't add one either.

A few months later they came back to me after hitting exactly the same issue. They needed to know how old certain records were and had no reliable way of finding out. In hindsight, they said adding a timestamp column would have saved them a lot of time and effort.

You could say that the torch has been passed on to another person - it's one of those mistakes you only make once but it stick with you forever.

Oh. And I can confirm that I have added a back log item to add this column to the table, as a priority!