When I create a new relational database table in my ColdFusion applications, my default operation is to add three columns: the primary key, a date/time for when the row is created, and a date/time for when the row is updated. Not all entities fit into this model (such as rows that can never be updated); but, for the most part, this core set of columns makes sense. The “updated” column has no semantic meaning within the application – it is simply a mechanical recording of when any part of a row is updated. The biggest mistake that I’ve made with this column is allowing the customers to attach meaning to this column. This never works out well.
In the data-access layer of my ColdFusion applications, the “updated” column is always included at the end of my UPDATE SQL query. For example, here’s a made-up method for updating “Widgets”:
component {
/**
* I update a widget with the given ID.
*/
public void function updateWidget(
required numeric id,
string name,
string sku,
numeric priceInCents,
) {
```
<cfquery name="local.results" result="local.metaResults">
/* DEBUG: widgetGateway.updateWidget(). */
UPDATE
widget w
SET
<cfif arguments.keyExists( "name" )>
w.name = <cfqueryparam value="#name#" sqltype="varchar" />,
</cfif>
<cfif arguments.keyExists( "sku" )>
w.sku = <cfqueryparam value="#sku#" sqltype="varchar" />,
</cfif>
<cfif arguments.keyExists( "priceInCents" )>
w.priceInCents = <cfqueryparam value="#priceInCents#" sqltype="bigint" />,
</cfif>
-- The UPDATEDAT column is always updated anytime any of the other
-- columns are updated. There is no semantic meaning to this column
-- within the application itself.
w.updatedAt = UTC_TIMESTAMP()
WHERE
w.id = <cfqueryparam value="#id#" sqltype="bigint" />
;
</cfquery>
```
}
}
As you can see, the updatedAt column is always modified with the current date/time whenever the updateWidget() method is called. It doesn’t matter which of the columns are being updated. It doesn’t even ensure that the column values are actually changing – it’s just a mechanical update.
In retrospect, it’s probably my mistake to ever include this “updated” date in the user interface (UI) of an application. I often feel pressure to make UIs “rich”; and, adding an “updated” date to the UI seems like an easy way to flesh out an interface.
But, the problem is, even if the “updated” value has no semantic meaning, by including it in the UI, the users will attach meaning to it automatically. And since I’m not educating them as to what this value means, they’ll just make up whatever story they want, logical or not.
And, the moment they do that, things go wrong. Suddenly, there are complaints that the value is changing when it “shouldn’t be“. Of course, since each user made up their own story, each user has a different mental model for when the value should change.
One user might say:
The “updated” date should only change when the price of the widget changes.
And, another user might ask:
Can you make sure the “updated” date is only changed when one of the widgets is sold to a customer?
Both of these things can’t be true. At least, not for the same database column. And, that’s the root of the issue: neither of these concepts should be attached to the “updated” column – they should manifest as separate data structures with their own consistent semantic meaning.
For the user who wants to know when the “price” was changed, we might have to add a column for priceUpdatedAt. Or, perhaps it makes sense to have some sort of a “price history” associated with a given widget. Or, maybe even have both approaches wherein the history is “eventually materialized” into the priceUpdatedAt column.
And, for the user who wants to know when a widget was sold, we might have to add a column for mostRecentPurchaseAt. Or, keep some sort of a order history, etc. etc. etc.
Ultimately, you should never show data to your users if the data has no meaning to them. Because, they’ll attach meaning to it whether you like it or not. And then, things are out of your control. The “updated” column in a database table has no semantic meaning – don’t start treating it like it does. I’ve made this mistake too many times.
If the “updated” column is just a mechanical recording of when the row was updated; and it has no semantic meaning within the application; and it probably shouldn’t be shown to the user (for all the reasons outlined above); then, you might be wondering why even bother having that column in the first place?
Honestly, I don’t think I can articulate a good reason. I do think that it’s a helpful data-point that sheds light on the life-cycle of a row; and, I do think that it sheds some light on how your customers might be interacting with row; but, that’s just a feeling in my tum-tum – I don’t think I can put a solid, coherent argument together.
Mostly, I just have a sense that it is good; and, I think I might need that column some day.
Not the strongest argument.
Want to use code from this post?
Check out the license.
