Is it too verbose to always have 'modified' time stamps on all entities within a database?

Question

The question can be summarized as:

In a database (regardless of type), would it be considered a good practice to always include updated, created (and possibly deleted) properties, for all entities, regardless of nature?

The question is asked in the abstract, but I'm looking for real life answers, common - and hopefully good practices here.

Is there a name for this convention?

---

In real life applications, data often stands at the core of a service. The data is what's of importance, the rest of the application is just various ways of presenting that data. I'm speaking mostly from the perspective of a web-developer, seeing as it is the industry I work in.

More often than not can logging and tracking of data be more important than the payload it carries, i.e.; it's OK to trade of a few bytes of bandwidth and disk-space for knowing when this 'hunk of data' last got updated. Having persistent data, is also commonly of great importance (speaking mostly for the deleted-property here). As knowing when something was deleted, is often always better than having... "a few missing ID's" in a table, at most. - You do of course, save some disk-space.

Bandwidth and storage are getting real cheap, just googling cost per gigabyte over time and similar for bandwidth; pretty much speaks the same language. Transferring and storing data is cheap and it is getting cheaper.

Some databases even roll with a time stamp as part of the ID, I'm speaking of course of MongoDB, although they were probably not the first to traverse this area.

Keeping the previous things in mind, would it be a 'bad idea', to implement a policy/practice to always persist data and to always implement 'auto-updating' (if possible) created, updated and deleted properties?

Answer 1

Let's take the example of a major ERP on the market:

• all the master data and most of the transaction data carry 4 fields: the author and the time stamp of the record creation and of the last record modification.
• all the critical field changes (it's customizable what is critical) are logged with the author of the change, the timestamp of the change, the old and the new value.
• data is deleted only via an archival process that keeps track of the archiving date.

The reason for this is not technical, but it's for audit purpose. For financial data (SOX) or in some industries (CGMP) such auditability is a mandatory (sometimes legal) requirement.

The first data remains with the data records as long as they are in the system for convenience. The second log is kept to be able to trace and explain changes, but can be purged periodically. By the way, all these time stamps of related data can permit to identify the inconsistencies that spot unauthorized tampering.

From the performance point of view, the largest corporations of the world use such a system and the gigabits of logs are a requirement that the hardware and software architecture and system sizing just have to cope with.

Of course this approach is based on an RDBMS. If timestamps and logging would be available at DB level such as for some NoSQL databases, they would be redundant.

So I think that the main question is not whether it's a best practice or not, but whether it's a requirement or not, and how it can be best implemented with the tools available.

Answer 2

would it be a 'bad idea', to implement a policy/practice to always persist data and to always implement 'auto-updating' (if possible) created, updated and deleted properties?

Persistence and auditability (not stated but implied) are valuable goals, and it is good to think in that direction for cases where you need them. That said, to answer your questions:

Yes. It would be a bad idea to implement these fields as policy on all tables. Not because of the space it takes, but because this implementation misses the mark on persistence, and the level at which audits occur.

Persistence is not only about tombstoning (deactivating) instead of deleting. Probably your #1 way of losing data is through updates. Whenever some values are updated, (in most databases) the old values are lost and along with them any insight they held. With only timestamps, you can't answer more poignant questions like: Who made this change? What did they change? and Why? Also, full persistence including updates could only answer the middle question and hint at the latter.

Answering the other two has to aim higher than just the database technology used... higher than CRUD operations. A CreatedOn (or created_on if you prefer) timestamp could tell you when an INSERT was performed on the table. But your boss isn't likely to ask that question. He might ask: is that order a quote conversion by a salesman? Or is it a web order? Or is it a drop ship order from a partner? created_on is not going to have that answer. When your boss asks you whether the order shipped, the Shipping table's updated_on stamp is not the right answer either (maybe the shipping info changed instead?). That's why CRUD timestamps are often (not always) the wrong level of auditability. "When" is an important question that is answered by timestamps, but only if it is connected to a business use case. In the shipping scenario, a shipped_on field might be the perfect answer, because it answers a business question, whereas updated_on only answers a technical one.

It seems what you are journeying toward in a round-about way is an Event Sourcing type of pattern, which is fully persistent and auditable. As part of the journey, you have to realize that the concepts important to you as a developer (e.g. CRUD operations) do not map directly to business concepts. The only way to make sure you record the information needed to answer the right questions is to talk to stakeholders and learn about the problem space from them.

Implementing CRUD timestamps on All The Things as policy is at best going to be wasted space (by having answers to the questions no one is asking), and at worst will be a distraction to answering vital business questions (by giving an answer to a different question than what was asked).

P.S. I do use stamps like CreatedOn sometimes. There's nothing wrong with that. Just make sure they answer useful questions.

P.P.S. If this question were on dba.stackexchange.com, then I might answer differently based on the technical merits of such an approach. But I believe this system design/architecture viewpoint better answers the question for this site.

Answer 3

No, it is not too verbose. I'd say it's close to being a best practice.

Having created and modified dates on a per record basis allows one to see how stale that particular data row is. Most systems I have encountered have data retention requirements. By having these dates, one can have a data purge process to keep only a certain amount of data in a rolling window of time. This saves storage space (money) and keeps the application from growing out of control or suffer performance issues. Any application should always have requirement(s) on how much long to keep transactional data. If could be a few months or years, but in any case, having these data markers can make data maintenance easier and allow for proper data storage capacity and planning.

Also, it can answer business questions like:

How much data has been added in the past month? How many updates have occurred in the past 24 hours?

So, having those data markers can help with those queries as well.

Answer 4

I think most applications should have them. These values are more of a convenience for trouble-shooting and other support needs. Soft deletes have benefits as well, but you always have to include them in your query logic. Just because you can use them, they're not enough. Many auditing needs require more data and sophistication even beyond logging.

Regulatory agencies may require such auditing as displaying a history of all chances. Your application may even be required to show what was changed, by who and when. This is a good reason to be careful how many fields you put into a single table.

Caution: Data and bandwidth may be cheap, but there are performance concerns. You'll find many installations get a humungous piece of hardware dedicated to the database. This is because once you out-grow it and start considering a cluster, it gets complicated and expensive.

There's more to YAGNI. You may have needed it, but now you don't know if you still need it so you're stuck with it. YMHNIBNYDKIYSNISYSWI. This is much worse. There should always be a reason for something. I think aiding trouble-shooting is enough, but if you find you don't really need it; get rid of it or don't put it in at all.