Select Page

A Database Column For “Date Updated” Has No Semantic Meaning, Nor Should It

Cyberdime
Published: October 27, 2022

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.

Check out the license.

Source: www.bennadel.com