none
Tractability - ModifiedBy and CreatedBy

    Question

  • Hi,

    I have just started a new project, and, I have just discovered that the database implements a 'tractability' requirement whereby every table has "ModifiedBy" and "CreatedBy" (and ModifiedWhen, CreatedWhen) columns for tracing changes to particular Users, as is standard practice.

    However, the database designer has implemented this strictly such that these columns are associated to the User table by Foreign Key constraints.

    Effectively, the entire database is connected to the User table by these constraints. And worse than that, there are two constraints per table.

    When I approach the database with .NET EntityFramework I was confronted by a mass of associations that were meaningless in business terms.

    After speaking with the database designer regarding my concerns, I was told that this was necessary to retain correct tracing information.

    I can't see how this is acceptable. I thought that these columns are intended to facilitate concurrency, and tracing of sorts, but not strict tracing, as it only traces the last user to make changes, which is of little help in data forensics anyway. And, I thought that heavy duty tracing ought to be conducted by Audit Tables using Triggers. 

    Moreover, the User table is associated by those fields and constraints to ... yes, the User table. Consequently, the constraints must be dropped to enter the first User record.

    Am I wrong? Is the kind of 'tractability' described above acceptable data architecture?

    • Edited by Simon Le Serve Wednesday, April 09, 2014 5:38 PM Additional description of problem.
    Tuesday, April 08, 2014 10:05 AM

Answers

  •  the database implements a 'tractability' requirement whereby every table has "ModifiedBy" and "CreatedBy" (and ModifiedWhen, CreatedWhen) columns for tracing changes to particular Users, as is standard practice.

    However, the database designer has implemented this strictly such that these columns are associated to the User table by Foreign Key constraints.

    Effectively, the entire database is connected to the User table by these constraints. And worse than that, there are two constraints per table.

    Those columns are the best friend of a DBA. Generally helpful in the short term since the previous update info overwritten by the last update info.

    However, those are table maintenance columns therefore 3NF requirements do not apply typically.  If the system hw/sw platform robust enough, it can be implemented that way.

    I would design it without the constraints and setup audit trail:

    http://www.sqlusa.com/bestpractices2005/auditwithoutput/


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Wednesday, April 09, 2014 7:03 AM
    Moderator

All replies

  • Hi,

    You would like to trace each and every update use Audit Tables using Triggers as all updates will exist.if not, use same above architecture(only one last history record will exist if more than one change happens).

    Thanks and Regards,

    Vanchan


    Tuesday, April 08, 2014 11:27 AM
  • Don't blame a crappy program and the its problems with multiple relationships on a bad design.  I would consider this approach unusual - you haven't provided information about how these columns are updated so there might be more to this story than presented.  Is it wrong?  If it works then the obvious answer is "No".  There are no absolutes.  Could it be "better" in some way?  Perhaps, but "better" is relative term. 

    Your phrasing of the 2nd-to-last paragraph sounds like it refers to organizational-specific practices.  I don't know how "tracing of sorts" differs from "strict tracing" nor what "strict tracing" might actually be.  Since you are new to an existing project, I suggest you proceed with caution when you start questioning everything that has been done before.  Typically a database is designed to support a specific set of requirements; after the fact questions from new members might not be looked on favorably.  Rather than questioning the correctness, perhaps you should ask about the requirements and how they are implemented.  Learn from what has already been done and see how a requirement was translated (well or poorly) into an implementation.

    Lastly, I don't really understand the term "tractability" - have never heard this term used in relationship to a database.  "Traceability" I've heard and seems to fit with your discussion. Usually it refers to the ability to know who did what and when did it happen.

    Tuesday, April 08, 2014 12:42 PM
  • "Traceability" refers to identifying who and when something happens.

    "Tracing-of-sorts" refers to the 'weak' record of information provided by fields like 'ModifedBy', 'WhenCreated'.

    "Strict-tracing" refers to the comprehensive trace provided by more complete auditing tables and triggers.


    Wednesday, April 09, 2014 1:45 AM
  •  the database implements a 'tractability' requirement whereby every table has "ModifiedBy" and "CreatedBy" (and ModifiedWhen, CreatedWhen) columns for tracing changes to particular Users, as is standard practice.

    However, the database designer has implemented this strictly such that these columns are associated to the User table by Foreign Key constraints.

    Effectively, the entire database is connected to the User table by these constraints. And worse than that, there are two constraints per table.

    Those columns are the best friend of a DBA. Generally helpful in the short term since the previous update info overwritten by the last update info.

    However, those are table maintenance columns therefore 3NF requirements do not apply typically.  If the system hw/sw platform robust enough, it can be implemented that way.

    I would design it without the constraints and setup audit trail:

    http://www.sqlusa.com/bestpractices2005/auditwithoutput/


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Wednesday, April 09, 2014 7:03 AM
    Moderator
  • thank you Kalman
    Wednesday, April 09, 2014 7:33 AM