Re: table versioning approach (not auditing)

On 10/6/14, 6:10 PM, Gavin Flower wrote:
> Even if timestamps are used extensively, you'd have to be careful joining on them. You may have information valid at
T1and changing at T3, but the transaction has T2, where T1 < T2 < T3 - the appropriate set of data would be associated
withT1, would would not get anywhere trying to find data with a timestamp of T2 (unless you were very lucky!). 

Yeah, this is why I think timestamps need to be shunned in favor of explicit pointers. Anyone that thinks timestamps
aregood enough hasn't thought the problem through completely. :) 

I also think there's potential value to storing full transaction information (presumably in a separate table):
txid_current(),txid_current_snapshot(), now(), current_user, maybe some other stuff (client IP address?). That way you
cantell exactly what created a history record. With appropriate shenanigans you can theoretically determine exactly
whatother history data would be visible at that time without using pointers (but man would that bu ugly!) 

> Actually things like phone numbers are tricky.  Sometimes you may want to use the current phone number, and not the
oneextant at that time (as you want to phone the contact now), or you may still want the old phone number (was the call
toa specific number at date/time legitimate & who do we charge the cost of the call too). 

Yeah, I'm pretty convinced at this point that history/versioning should be built on top of a schema that always
containsthe current information, if for no other reason than so you always have a PK that points to what's current in
additionto your history PKs. 
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com