John W Colby
jwcolby at gmail.com
Tue Mar 25 06:51:30 CDT 2014
>>...but I would tend to use a real timestamp than going to a random number generator. This is a built-in data type with SQL Server and the update happens with no triggers or any other action by the developer, it is built in to the database engine. If the field exists (and there can be only one of this datatype per table) then an update automatically causes this field to be updated. If you go with a real date/time field then you have to perform the update with a trigger. >>...A file handling transaction logs can be used to document and record data changes... Yes, and that is additional processing on the developer's side. I am discussing a bare bones system ATM. >>...another technique that I used when modifying a record was to AND and OR every field in the record, The built-in timestamp field in SQL Server performs this very functionality for you. >>...deleted records have always been an issue. I agree, an active / trash flag pair was my solution (in the past) as well. I was not given the liberty at the IBM gig. John W. Colby Reality is what refuses to go away when you do not believe in it On 3/25/2014 1:23 AM, Jim Lawrence wrote: > I like the method you use: > > ...but I would tend to use a real timestamp than going to a random number generator. Considering a system can create a timestamp number with six places below the decimal point, the odds of a data conflict is almost mathematically impossible. Additionally, a record can be tagged (a field) as being in "process mode" or just have a field where the current-user code is stored (this will allow the situation where a single user is working from multiple stations) and again only then does the user system have to be concerned with possible modification conflicts. > > ...A file handling transaction logs can be used to document and record data changes...this is particularly important with invoicing as these data records are very active and can be used by multiple people, sometimes at the same time. > > ...another technique that I used when modifying a record was to AND and OR every field in the record, store that resulting number and when about to update the record, run the process again. If there was a difference in the two values, then handle it. > > ...Through overnight processing all flags would be cleared by locality and/or inactivity duration. > > ...deleted records have always been an issue. In my way of thinking records should never be deleted and should only be toggled/flagged active or inactive. If some user is somehow in the process of modifying a deleted record it can be easily handled (This also resolves the serious error on all big active databases where a situation called "the deadly embrace" used to be able to freeze a system up and require the DBA to unlock it). There should always be a garbage collection system that archives records that have been truly deleted...even then nothing is ever completely deleted. Sometimes records have to be recovered from archive; again not a complex issue. > > I have used these methods in most of my databases and in over twenty years and millions of records there are probably only a couple of situations, I can recall that manual intervention was required and then all the data was available in the transaction logs and archive files...and the causes for these issues were never discovered and I suspect a internal database failure but that was outside my realm of expertise. > > Jim > --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com