Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82

    Audit fields in Subtype/Supertype Model

    I'm using the subtype/supertype model and have some basic audit fields (created by, created date, modified by, modified date). Question is, do these fields need to exist at the subtype level tables as well, or is it sufficient to only have these at the top-level table, and any time an update is made to the subtype tables, simply trigger an update to the supertype.

    Thanks in advance,

    -A

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that sounds like a great idea

    both ways

    or either

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Looking for a serious reply; I know it might be too much to ask

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i was being serious, just concise

    it depends on what you need

    if only the main supertype attributes need to be tracked, then don't put the audit columns in the subtype tables

    if you have attributes in the subtype tables that need to be tracked, then you would put the audit columns in there -- possibly triggering a subtype attribute change up to the supertype, possibly not

    you revealed nothing about your particular supertype/subtype structure, and nothing about your particular auditing needs

    so of course the serious reply is: "it depends"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I would not update the audit fields on the parent records just because a child record is modified.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd actually agree with Rudy wholeheartedly. Either, both, or neither as you need them.

    The idea of "tagging" rows with audit metadata like creation date/user and update date/user exists outside of conventional modeling. These attributes don't contribute or detract from the basic model, and they aren't used by the basic model. They exist only to serve whatever auditing needs you've got, so include or exclude them as your needs dictate.

    The way that I perfer to handle this is via an audit table. When auditing, I like a trigger to copy the "interesting" columns to an audit table along with the user information, machine information (IP address and name if that is available), and datetime. The initial insert of data leaves a completely NULL audit row, so I get the creation information at no charge and in a consistent format. This also gives me the ability to see who changed what when, and to even roll that back if necessary.

    -PatP

  7. #7
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    In this instance, the tables store ad campaign information, and based on the campaign type, a different sub-type table is used. A campaign can only belong to one campaign type. So in this case, my thinking was why repeat the audit fields in every detail level table since the sub-types are logical extensions of the super-type.

    Thoughts?

    -A

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    If any change to a subtype implies a change to its supertype, and your auditing doesn't care about changes to the subtype, then your case for only auditing the supertype makes sense. This is very, VERY rare, but it could happen.

    In most cases, auditors want to know who changed what, when, and often want to know what the previous value was too.

    -PatP

  9. #9
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Quote Originally Posted by Pat Phelan
    In most cases, auditors want to know who changed what, when, and often want to know what the previous value was too.

    -PatP
    Exactly. The way I see it, we either implement full auditing where we know the previous values; new values, etc. or, we simply put these basic audit fields at the super-type level. I don't think repeating these audit fields in each sub-type adds much value.

    -A

  10. #10
    Join Date
    Feb 2008
    Posts
    43
    Even if the Audit fields are either on the supertype or on the subtypes tables, or if you add only one Audit field set to only one of them, I'm thinking now how can you retrieve this kind of inf?

    Assuming you want to keep a history of your previous records (otherwise the Audit fields wll hold only the last change whitch doesn't make much sense) , how can you combine the archive tables for the supertype and subtype to have a full image of the whole record(supertype+subtype) that was at one point valid.

    I couldn't find any solution except for all the time in the two history tables, whenever there is a change in one of them, you make a copy of the other one as well and link them some-how.

    I hope I'm not complicating things more, so please correct me if I'm wrong.

Posting Permissions

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