How to DRY record metadata in SQL Server There is a database that has been given to me, and all tables contain these columns:


CreatorUserId
CreatorUsername
CreationDate
ModifierUserId
ModifierUsername
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?
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)  &lt;--  ThingID    (FK)       Users
Property1          Action                -------------
Property2          UserID     (FK)  --&gt;  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)  -----&gt;  ThingID (PK)  &lt;---  ThingID    (FK)       Users
Property1             |                         Action                -------------
Property2             |                         UserID     (FK)  --&gt;  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)  &lt;----'
ThingID (PK)  &lt;----  ThingID    (FK)            Users
Property1            Action                     -------------
Property2            UserID     (FK)  -------&gt;  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.