Where do you save your Create By/Date and Modify By/Date information?

Question

Where do you save your Create By/Date and Modify By/Date information?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Naked Ape Ten Centuries Points: 1095 More actions · Answer 1

I tend to keep the audit columns in the same table, and don't worry about their position.

An alternative to splitting your Customer table into separate Master and Supplemental tables may be to turn that structure around - keep a single Customer table, and create Master and Supplemental views with only the required columns.

Chris

GSquared SSC Guru Points: 260824 More actions · Answer 2

The order of the columns in the table shouldn't matter. If it does, views are the way to go.

Personally, I consider this pattern of data auditing to be more harmful than helpful. It gives the apparency of auditing without the actuality of it. It does let you identify rows created/modified in a certain date range, but the "ModifiedBy" column will end up lying to you.

Why do I say that?

Imagine this scenario:

You create a row of data. It is correct.

I edit the row, entering incorrect data in one column. I'm now the ModifyBy.

You edit the row, changing data in a differen column than I did. You're now the ModifyBy.

Management notices the incorrect data I entered. Someone checks the ModifyBy. You get in trouble because there's now no record I messed it up.

Even the ModifyDate column presents a problem:

1 Jan 2011, you create the row. ModifyDate either null or 1 Jan 11.

2 Jan 11, I modify the data. ModifyDate 2 Jan 11. I do something wrong on it.

Yesterday, you edit the row. ModifyDate 29 Jan 12.

Today, someone reviews the date, finds that it's wrong, but it's only been wrong since yesterday per the ModifyDate. That probably doesn't matter, so the data is quickly corrected, and nobody thinks to check if, for example, pricing has been wrong for over a year. After all, per the ModifyDate, it was changed yesterday.

Add in a dozen changes over time, and you only have who made the most recent change and the timestamp for that. No real way to track who changed what when, in what sequence.

So, what ends up happening when this is discovered? Someone demands that a real auditing solution be implemented. All rows changes are tracked, possibly through triggers, possibly through a passive audit (tran log perhaps), possibly through Change Data Capture, possibly through some 3rd party auditing tool. Now you have the "last changed" audit columns being audited by a real solution, and they become completely redundant.

So, in my mind, either actually audit changes to data, which requires a lot more than those four columns, or decide the data doesn't need auditing leave those off.

If all you really need is create date and last modified date, keep those, but it's unlikely you'll need those only and not a real audit solution. (Note: Unlikely != It'll Never Happen. Edge cases that need last-mod audits, but not real audits, will exist. But they're very, very rare.)

Most data either needs no real tracking, or needs something more comparable to source control. How useful would source control be if all you could get out of it was the orignal author, the date it was first checked in, and the most recent editor and last check-in date, without actually being able to restore to prior states, view changes made, et al? Not very.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

GSquared (1/30/2012)
Personally, I consider this pattern of data auditing to be more harmful than helpful. It gives the apparency of auditing without the actuality of it. It does let you identify rows created/modified in a certain date range, but the "ModifiedBy" column will end up lying to you.

Excellent point. Perhaps TYPE 2 SCDs are better for such a thing if auditing is truly what is necessary.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 4

Jeff Moden (1/30/2012)
GSquared (1/30/2012)
Personally, I consider this pattern of data auditing to be more harmful than helpful. It gives the apparency of auditing without the actuality of it. It does let you identify rows created/modified in a certain date range, but the "ModifiedBy" column will end up lying to you.
Excellent point. Perhaps TYPE 2 SCDs are better for such a thing if auditing is truly what is necessary.

+1

If you really want proper auditing, it always comes at a price.

Roy Ernest ( @RumblingDBA ) recently published his series of articles on that topic here at SSC.

First part can be discovered at What, When and who? Auditing 101[/url]

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me