6

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?

Improve this question
  • 1
    My database also has accessed which has been extremely useful in the past. – Jerry Jeremiah Jun 21 '16 at 19:49
  • Added value, but will cause a lot of writes on huge databases (and can also be offset with cache). But could possibly be a good idea on databases with fewer users. – die maus Jun 21 '16 at 20:00
  • "The data is what's of importance, the rest of the application is just various ways of presenting that data." - If that were true, then there would be no need for other web developers when Excel online and Google Spreadsheets exist. – Kasey Speakman Jun 21 '16 at 20:26
  • I think you are thinking of it too narrowly. In a broader perspective, look at google maps; the mapping service. What does it do? Presents data; you query the service, it presents data. The data is just "formatted" in a very human-understandable way, but it's still just data, presented in a very specific way. You could go about having a spread sheet for all that data, it would suck, but it would work. – die maus Jun 21 '16 at 20:44
  • The service doesn't just regurgitate data. It attempts to answer the questions you ask of it and present it in the appropriate form. When you switch from map view to directions, the view changes along with the data. Good applications are a beautiful dance with data being only one participant, but perhaps the main one that causes it to be interesting. – Kasey Speakman Jun 21 '16 at 22:29
4

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.

Improve this answer
  • 1
    This type of retention has never been applied in any of the projects that I've worked on over the years. It has never been a requirement, so I had not thought of it. Very interesting expertise, thank you. – die maus Jun 21 '16 at 21:09
4

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.

Improve this answer
  • The question basically boils down to, is that little extra disk space worth sacrificing for these 2-4 (maybe useless) columns. Regardless if they are going to get used. – die maus Jun 21 '16 at 22:47
  • The disk space is negligible (up to 8 bytes per field in most SQL dbs). However it is a mistake to disregard whether recorded information is useful. Anecdotally, many times I have been asked a business question and the only answer I could provide was a technical one which missed the mark due to my own such designs. – Kasey Speakman Jun 21 '16 at 22:58
  • Timestamps are more for support and trouble-shooting than a business case. As you mentioned, most business cases usually need more information. – JeffO Jun 21 '16 at 23:40
  • I'd say usefulness for troubleshooting depends on the usage of the data. We have such time stamps on several important tables, and it gave us better guesses than we would have without them. We still ended up using a separate event log to record important events so that we can directly answer the questions with minimal guesswork. I have used timestamps for maintenance at times. But we don't have or need them on all tables. – Kasey Speakman Jun 21 '16 at 23:56
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.

Improve this answer
  • > How much data has been added in the past month? How many updates have occurred in the past 24 hours? ... That is something I even forgot to add in my initial question. This is of course of utter importance, especially to upper management. Thank you. – die maus Jun 21 '16 at 21:05
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.