Last update date: Antipattern?

Question

I continue to observe how questions pop up that refer to a column in a database table called DateLastUpdated. I do not understand.

The only companion field I've ever seen is LastUpdateUserId or such. There has never been an indicator of why an update occurred; or even that there was an update.

In addition, this field is sometimes written from a trigger, where even less context is available.

Of course, he is not even close to being an audit; so this cannot be an excuse. And if there is and conducts tracking somewhere in the log or something else, this field will be redundant.

What am I missing? Why is this template so popular?

Answer 1

This field can be used to determine if there are conflicting changes made by different processes. When you retrieve a record from the database, you get the previous DateLastUpdated field. After making changes to other fields, you send the record back to the database level. The database level verifies that the DateLastUpdated you are submitting matches the one that is still in the database. If it matches, an update is performed (and DateLastUpdated is updated to the current time). However, if this does not match, then another process has changed the record at the same time, and the current update may be interrupted.

Answer 2

It depends on the exact circumstances, but such a timestamp can be very useful for auto-generated data - you can find out if you need to recount something if the dependency subsequently changes (this is how the build systems calculate which files are needed to recompile).

In addition, on many websites, the page will be labeled “Last Modified,” especially news sites that can edit content. The exact reason is not necessary (and there are probably backups if an audit is necessary), but this data should be visible to the end user.

Answer 3

Such things are commonly used for business applications where a user action is required to initiate an update. As a rule, there will be some kind of business application (for example, a CRM desktop application), and for most updates there is only one way to make an update.

If you are viewing address data, this was done on the "Save Address" screen, etc.

Such a database audit is intended to audit the business level, and not to replace it. Sometimes call centers (or always in the case of financial service providers in Australia, for example) record phone calls. This part of the audit trail is also not inclined to be part of an IT solution, as the desktop application (and its associated infrastructure) is in progress, although this is by no means a hard and fast rule.

Call center personnel also usually have some kind of “Notes” or “Journal” functions, where they can enter arbitrary text regarding why the customer called and what actions were taken so that the next operator can pick up where they are stopped when the customer returns.

Triggers are often used to record exactly what was changed (for example, writing an old record to an audit table). The purpose of all this is that with all the information (notes, recorded call, database audit log and logs), the previous state of the data can be restored, as well as as a result of the action. This can be a search / elimination of errors in the system or simply as a process of resolving conflicts with a client.

Answer 4

This is certainly popular: for example, rails have an abbreviation, as well as a creation timestamp (: timestamps).

At the application level, this is very useful, since the same scheme is very common in representations - look here, for example, questions (answered 56 seconds ago, etc.).

It can also be used retrospectively to generate statistics (for example, what is the growth curve of the number of records in the database).

Answer 5

there are several scenarios

Let's say you have an address table for your clients, you have a CRM application, the client rings that his address has changed a month ago, and the LastUpdate column shows that this row for this client was not affected after 4 months.

usually you use triggers to populate the history table so you can see the whole other story, if you see that the creation date and the updated date are the same, it makes no sense to hit the history table, since you won't find anything

you are calculating indices (stock market), you can easily see that it has been recalculated just by looking at this column

there are 2 database servers, comparing the date column, you can find out if all changes have been replicated or not, etc. etc. ect

Answer 6

This is also very useful if you need to send feeds to clients that are delta channels, these are only records that have been changed or inserted since the last feed was sent.