Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    UID (non-PK) in all tables as a general rule?

    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.

    Your thoughts please.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hi tim

    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




    rudy

  3. #3
    Join Date
    Nov 2002
    Posts
    2
    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.

    Thank you!



    Originally posted by r937
    hi tim

    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




    rudy

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: UID (non-PK) in all tables as a general rule?

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •