I serve in the roles of Project Manager and Content Manager and aspects of DBA for our web development effort (we're a small book and magazine publisher). In part, I am charged with moving us toward a Content Management System.
I would appreciate comment and perspective on the following:
I have asked the programmers that I manage to incorporate as a general DB wide constraint the following columns in our MS SQL tables: numerical UID (non Primary Key), [creationDate], [modificationDate] (updated via a trigger on any change to a record).
Part of my motivation for this is to facilitate management, administration, reporting, workflow and publishing process. In addition this would seem to me to be a minimal amount of metadata in a similar vain to the creation and modification dates associated with flat files.
I am getting some resistance from one of our programmers who I believe is inappropriately concerned with keeping the number of columns in any given table to an absolute minimum.
Thank you Rudy for your timely, wise and balanced response!
The numerical UID is primarily for utility pourposes. It is intended as another means of ordering that other IDs wouldn't quite facilitate. [creationDate] comes close but would fail to make apairent a deleted row for instance.
I appreciate your last comment. Part of the dynamic involves illdefined roles, responsibilty and authority coming down from our director--a somewhat dysfunctional situation.
Originally posted by r937
the programmer has done his job in stating a concern, but that's as far as he should carry it
the additional space and processing overhead of these columns is not a sufficient counterargument for the benefits of change tracking
and further resistance on his part would be politically naive
with microsoft sql server, as the database grows, you simply throw more hardware at it, end of discussion
i don't understand the numerical aspect of the uid -- is this the userid of the person who made the addition/change?
having been a database manager for years, i should also tell you that these ideas take hold much more readily if they are proposed by the troops and not imposed by the boss
If I were you, I'd go through this simple excercise:
Write down the predicate for each table. What would "createDate" and "modifyDate" mean? What does the UID mean? (IMHO, the biggest problem with surrogate keys is that they're basically meaningless.) How will this data be represented to the user? (Note that, IMHE, UIDs will inevitably be revealed to users. That's very bad UI design.)
[creationDate] comes close but would fail to make apairent a deleted row for instance.
A failed transaction can, in many SQL DBMS's, cause a sequence to skip.