Jim Dettman
jimdettman at verizon.net
Fri Mar 7 13:44:21 CST 2014
The same thing can be accomplished with a log table. I wrote an HR system like that once, where it was possible to view the employees record "as of". I basically did what a DBMS does when it rolls back. The effective date though sounds like a better approach. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Friday, March 07, 2014 11:11 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Membership File Changes I think that it's worth pointing out that in some db designs, tracking the Last Modified date-time is woefully inadequate. Why, you might ask? The reason is that by definition Delete and Update statements destroy data, making it impossible (without a Restore) to go back to the previous data. In general, these designs are called Point In Time Architecture. For example, consider a medical database in which a Patient table might have a FamilyPhysianID column. Patients move and therefore change Physicians; Physicians retire or die; and so on. With simple Updates you automatically destroy the history. The most common approach (detailed in my Simple-Talk article on this subject) is to add a pair of columns to each table, EffectiveDate and EndDate, and to eschew traditional Updates and Deletes. If you're interested in the details of such a design, visit www.simple-talk.com and search for "PTA Architecture" (The editor didn't like the traditional acronym :) Arthur On Fri, Mar 7, 2014 at 10:23 AM, Bill Benson <vbacreations at gmail.com> wrote: > I can tell you are not sure. > > I am making the point that to my knowledge, it is not possible to compare > one GUID and determine that it was created either before, or after a GUID. > So I don't know why you reply to that "... and someone's already pointed > out > where you'd want to use a GUID > rather then an AN." -- as this is exactly why I WOULD NOT want to use a > GUID > as opposed to an AN. > > > >> . If you reset the seed value of an AN to the max value of a long, the > next number > assigned will be negative, thus invalidating your check>> > > ... and who in their right mind would do this? As you wrote, ". I've never > seen anyone do it, yet"" > > > >> I certainly would not be relying on an AN field for that. You should > have > an entry date/time and a transaction date/time. > Well that's your prerogative, sir! I take a different view entirely, and it > is because adding records in a batch operation, I will often stamp them all > with the same date/time. I am pretty sure the system date is not precise > enough to measure a distinction between records added "simultaneously", so > using your technique, you would not, in my estimation of database > precision, > be able to sort out which record were added first in an operation. With AN, > you don't have that problem. I just sequence records by my LastModifiedID > and the AN field itself, then it doesn't really matter even if someone else > inserts rows in a record-locking (rather than page-locking) operation. > > Different strokes for different folks, I have seen no reason to give up my > reliance on Autonumber and I am planning on continuing to make them PKs and > have other tables fill up their FK fields with pointers to those, and > probably would never use GUID unless I am on a project much larger than > myself, for performance reasons and for the reason I just mentioned, that I > have no rational way to compare records for sequence (and datetime is > useless, despite the protest I hear you about to make :0 ) > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman > Sent: Friday, March 07, 2014 9:37 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Membership File Changes > > > Not sure where were going with this, but a couple of points: > > <<Jim, so you have agreed that GUID1 cannot be compared to GUID2 to tease > our > a record's ordinal position>> > > Yes and someone's already pointed out where you'd want to use a GUID > rather then an AN. > > <<and you have said that auto number can get > confused and turn negative. >> > > No. You can as a matter of normal operation, roll over to a negative > value on an AN. I've never seen anyone do it, yet. The max value of a > long > is quite high and I doubt an app would be able to create that many records > in it's lifetime. But it is part of the definition/logic of an AN. If > you > reset the seed value of an AN to the max value of a long, the next number > assigned will be negative, thus invalidating your check. > > << No long as the database is not replicated or being > maintained client-side, the connection can be dropped and the record used > until update is required or the next New Record.>> > > While that's true, but the point you miss is that with GUID's, a > connection does not need to be made at all for data entry, replicated or > not. So in the case were I have records created at various locations, then > merged later, GUID's are a perfect choice. > > The only other option is to use AN's, then when merging, prefix the key > in > some way to indicate the source. That works pretty well actually and you > get better performance then a GUID, but then your stuck managing the > process > and open the door to possibly making a mistake. > > <<I cannot think of a database project yet where I was not > concerned with sorting records in a recordset that included all records > posted on or before a certain record, or on or after a certain record, and > datetime is not a good predictor. I have seen that field > hand-entered/overridden after-the-fact. >> > > I certainly would not be relying on an AN field for that. You should have > an entry date/time and a transaction date/time. The first never being > allowed to change. > > Jim. > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson > Sent: Friday, March 07, 2014 09:00 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Membership File Changes > > Jim, so you have agreed that GUID1 cannot be compared to GUID2 to tease our > a record's ordinal position and you have said that auto number can get > <<snip>> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Arthur -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com