Database Object Naming Standards
Part IV: createUdt and modifyUdt
In Part III of this series, I talked about the naming of key columns. This time, I turn my attention to two fields which really should be in every table: when attempting to track down problems in the database, a very useful thing to know is the date and time the data was first created, and when it was most recently modified. As part of a standardized approach to database design, I have gotten into the habit of adding two columns to every table definition; createUdt and modifyUdt that provide this capability.
What’s In a Name? If you’ve read the post on column naming standards, you will recognize the Base Name udt. This means the column reflects UTC time, not local time. This is a nod toward internationalization, which I think every application should take into consideration, particularly in the early stages of design and implementation. The prefixes create and modify are self-explanatory, and are required, of course, to differentiate between the two types of dates in a given table definition. Finally, the two dates have equal status and value, and therefore they both have prefixes.
NULL and DEFAULT The columns createUdt and modifyUdt should be specified as NOT NULL, because they always need to be populated. Also, assuming your particular relational database has a UTC date function (most do), the default for createUdt and modifyUdtshould be specifed to use the UTC function so that they get the right date and time when the record is created.
Keeping Them Right If these columns are going to be useful, they should never be directly manipulated by your application. In order for them to be useful, you have to have absolute faith that they really do reflect the date and time of creation or modification, and not somebody’s interpretation of these facts. To ensure this, I use a trigger (the ancient SQL Server 2005 code is shown below for illustrative purposes only — your code may well be different) that automatically corrects these values in the event that they are directly changed in some way. This is slightly different in either case; createUdt gets set back to its original value, whereas modifyUdt is always set to the current system date and time.
CREATE TRIGGER
DogTrg
ON
Dog
FOR
UPDATE
AS
BEGIN UPDATE
Dog
SET
Dog.modifyUdt = GETUTCDATE()
FROM
Dog AS Dog INNER JOIN
INSERTED AS INSERTED ON INSERTED.uid = Dog.uid UPDATE
Dog
SET
Dog.createUdt = DELETED.createUdt
FROM
Dog AS Dog INNER JOIN
DELETED AS DELETED ON DELETED.uid = Dog.uid
WHERE
Dog.createUdt != DELETED.createUdtEND
Of course, if you intend to use this trigger in your own application, you will have to change the table name. Also, a really determined user can always delete or disable the trigger, but let’s assume that it keeps the honest people honest, 99% of the time, and that you have set up rights in such a way that only person with a need to see the trigger code, can. With a trigger such as this set up for each table, you can then entirely forget about keeping these columns updated — it’s entirely automatic.
It always surprised me that database vendors do not have these capability bundled right out of the box — I can remember using databases in the dim and distant that had this data available as a standard feature of every table definition. Heck, maybe modern databases do, and I just haven’t found the function yet. Thank you very much for reading, and let me know what you think.
©2019 Intellog Inc.
This series of articles was originally published by Terence C. Gannon in 2007 in a primordial version of The Intellog Blog. As we undertake a brand new, clean sheet database design for a new client, we decided to go back to these source documents to see if they have stood the test of time. For the most part, they have, but we also took the opportunity to update them a little for a more modern era. We would love to hear your feedback.
