I would like to have a table pattern on my db. I.e. every column should have

  • Record_Insert_Date
  • Record_LastChange_Date
  • Record_User_Insert
  • Record_User_Update

The obvious approach is to add these columns on each tables, but is not so easy to mantain. I would like to have this informations shared across db in a single table. Schema of this table should be somthing like

  • Record_Insert_Date
  • Record_LastChange_Date
  • Record_User_Insert
  • Record_User_Update

The questions are

  • how to join with other tables (two possible options are)
    • 1) Use sys.tables table id as FOREIGN TABLE ID. May change? Should be dangerous in case of db refactory? Performance of query on sys.tables to retreive table name to join dynamically?
    • 2) Add an extra field each table on db to have an integer code that identify it. I.e tableA.TABLE_ID=1, tableB.TABLE_ID=2, ..., tableXX.TABLE_ID=N. Using this approach a simple join is enough to match record. Difficult to use is a table have a multiple key.
    • 3) REVERSE OF 2) Add an extra field each table on db to have an FK to shared table. In this case shared table have an IDENTITY() key instead of (FOREIGN_TABLE_ID, FOREIGN_ROW_ID)
  • performance of each approach?

I have a couple of questions:

1) Do you need to keep a detailed history of changes? Or only who/when was the record created and who/when last updated it?

2) Assuming no detailed history is needed, the main question I have is whether your alternate solution, of using a separate tracking table(s), will be any easier to maintain than adding the columns to every table. You will either need to: a) add code into every query/stored procedure that manipulates data to update the tracking table; b) add a trigger to every table that needs to be tracked.

3) How often do you expect to query this data? My experience with this type of auditing is you only need it when something bad has happened.

My impression of your options are:

1) I think using the Object_id for the tables as your FOREIGN TABLE ID is a good idea. Why would this change? Do you expect to drop and recreate tables during deployments? And if you do, you can easily map the old object_id with the newly created one and update the tracking table. You could add this logic to your deployments.

2) Wouldn't this negate your reason for not adding the tracking columns into the main tables?

3) See comment for 2), plus I don't think this would work as well as 2).

As far as performance, if you use a separate tracking table, I think it would be best to keep it as separate from the main tables as possible (option 1).

The biggest potential performance boost I can see by using a tracking table is for querying the data. You will be able to tune the indexes specifically for those queries. Also, all your data will be centralised. If the columns are on the base tables additional indexes will probably be an overall disadvantage. BUT!!! Back to my question 3): How often are you going to need to query this data?

As far as day-to-day performance your real impact will come down to how you implement updates of the tracking table. Changes to the stored procedures would be better than triggers in my opinion.

I'm sorry if I haven't really answered your question, but I hope my comments help.

  • 1) This is not for tracking (creation+lastmodify is enough). 2) Maybe you are right, but I'll need to add the same columns on each table and this sounds like a duplication. 3) I would like to use for user authorization on records, so it would be used every time I query on a table. (If I used 10 tables on my query I'll need 10 joins on tracking table, this is the performance concerning about) OPTIONS 1) Managment studio in some situation recreate the table (i.e. add an identity to an existing primary key) so id could change, this is my doubt. – user1299153 Jun 4 '15 at 13:33
  • It's risky maybe. 2) Maybe yes but this is a constant column (default) 3) Maybe yes but this is just one column to join the external table – user1299153 Jun 4 '15 at 13:44
  • All in all my sensation is maybe is better to simply add columns to each table. – user1299153 Jun 4 '15 at 13:46
  • I do think that adding the columns to the tables is the best option. it is the most obvious and most transparent solution, IMHO. – tommyfly Jun 4 '15 at 14:11
  • Ok, tks for your feedback – user1299153 Jun 5 '15 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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