Many packaged and custom applications store a few standard metadata elements in the database. This article lists them and explains their purpose and how they are implemented.
Those metadata fields help to track and audit data. In a perfect world all the databases would have those fields by default. In this imaginary world database platforms would support them out of the box for each entity/table.
One of the most common metadata fields you can find in databases is a data and time when record was created (inserted into table).
Knowing when specific record was created might be very useful for auditing or even analytics. It tells you how old particular row is and what was the order of creating data rows.
In most database systems it is enough to create a date or datetime column and set up a default value with current time:
Knowing who created a record is very useful in auditing. You know who to talk to. Or blame.
There are two approaches to this:
To use database login create a varchar column and set up a default value with function that returns logged in user (or use a trigger):
Similarly to created by and time fields databases often have time and login of user who last updated row. Again, very useful for auditing and trying to figure out the origin of the data.
Having information who and when updated row gives you information if it was ever updated after its creation. You know how fresh the data is and who to talk with if you want to get more information about its status.
To use database login create a varchar column and set up a default value with function that returns logged in user:
Less obvious and less common metadata field is a source of a record. This can be an identifier of an application/module, interface or program that created each particular row.
It is a wise strategy to have this field in your tables, for key tables at least. Data can be collected over a long period of time from various sources with various programs.
Examples of different sources:
It is very easy to create such field (or fields) but making sure it holds the right information takes a lot of conscientiousness at each stage of data management - application design, development, data integration and migrations. This needs to be considered with each insert statement written or executed at any stage. It a good practice to have a reference documentation with a list of possible values.
This field is quite rare. This is used mostly for the implementation of optimistic locking mechanism where application handles multiple users by checking by row version number whether row was updated by someone else before saving it.
Besides handling concurrent updates this also brings extra information and can be used for auditing and analytics. It tells you how many times specific row was updated by users.
This can be implemented with a trigger or in application layer. Optimistic locking needs to be implemented in the latter.
I hope you found my compilation useful. Do you know any other standard metadata field is stored by applications? Please share in the comment.