Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There is a database that has been given to me, and all tables contain these columns:

  1. CreatorUserId
  2. CreatorUsername
  3. CreationDate
  4. ModifierUserId
  5. ModifierUsername
  6. ModificationDate

However, it seems that this design is not DRY and it's really hard to maintain because this database contains like 300 tables.

On the other hand, the modification history and creation history and lot's of other actions on the database level matter to us.

Is there any better way to record any DDL and DML operations?

share|improve this question
    
By what mechanism is data added/changed in this database? (I.e., ORM, stored procedures, application SQL, etc.) –  Jon Seigel Sep 15 '13 at 12:51
    
We use ORM @JonSeigel –  Saeed Neamati Sep 17 '13 at 5:49
    
Yep, that's what I figured (and why I asked). I suspect you're going to have a difficult time normalizing structures like this with an ORM involved. –  Jon Seigel Sep 17 '13 at 16:23

1 Answer 1

DDL (Data Definition Language: create/alter/drop/...) and DML (Data Manipulation Language: insert/update/delete/...) operations will need to be logged separately as the first affects the structure of the tables (and other objects) where the second affects the contents of those objects. I'll only deal with the DML side (when your application modifies table contents on behalf of a user) here.

Either way keeping the user ID and user name separately is not normal form - unless one isn't direclty modified (i.e. if the name is maintained as a computed column) you could potentally end up with data inconsistency. Also if you are trying to maintain an audit of data modifications you should have some sort of timestamp against the action. A generic arrangement might look like:

                   ActionAudit
ThingTable         ---------------
------------       AuditRowID (PK)
ThingID (PK)  <--  ThingID    (FK)       Users
Property1          Action                -------------
Property2          UserID     (FK)  -->  UserID   (PK)
   ...             TimeStamp             USerName
PropertyN

(where @action@ in the audit table is an enumeration representing insert,update,delete)
Of course you now have an extra table to maintain as whenever rows are affected in ThingTable you need to add an entry in AuditTable too, but you do have a complete list of who changed to the data not just the most recent user to do so.

If you want to keep a copy of the user ID (and the name too if you like) that inserted or last updated a row in ThingTable, then you could use a computed column or maintain those columns by a trigger that fires when an entry is made in ThingTable.

If you want to audit multiple objects this way, you either need an action audiot table per other object, or you need to drop the FK relationship with the ThingID in the one table, or you need a general Things table to stay in normal form, like so:

                                                ActionAudit
ThingType1Table             Things              ---------------
---------------             ------------        AuditRowID (PK)
ThingID   (PK, FK)  ----->  ThingID (PK)  <---  ThingID    (FK)       Users
Property1             |                         Action                -------------
Property2             |                         UserID     (FK)  -->  UserID   (PK)
   ...                |                         TimeStamp             USerName
PropertyN             |
                      |
                      |
ThingType2Table       |
---------------       |     
ThingID   (PK, FK)  --'  
Property1                   
Property2                   
   ...                      
PropertyN

If you need to note what changed each time then you'll need to add some way of recording that, but as all you are recorindg right now id the user I assume you don't need this level of recording. If you did you could do something like:

                     ThingFullAudit
                     -------------------
                .--  ThingID    (FK)
                |    AuditRowID (PK, FK) --.
                |    Property1             |
                |    Property2             |
                |       ...                |
                |    PropertyN             |
                |                          |
                |                          |
                |    ActionAudit           |
ThingTable      |    ---------------       |
------------    |    AuditRowID (PK)  <----'
ThingID (PK)  <----  ThingID    (FK)            Users
Property1            Action                     -------------
Property2            UserID     (FK)  ------->  UserID   (PK)
   ...               TimeStamp                  USerName
PropertyN

(that is based on the audit table per other object version: I'll leave merging this concept with the single audit table for all objects idea as an exercise for the reader!)
You can maintain the ThingFullAudit table using a trigger on ActionAudit, assuming the row in ThingTable is added/modified before the row in ActionAudit is inserted, that copies the contents of ThingTable to ThingFullAudit. You can then see what changes each user made in each update.

Of course this may all be massively over-kill for your logging requirements - you'll need to describe what the existing columns are trying to acheive in order for us to give you a more specific and concise answer.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.