[AccessD] Membership File Changes

Both approaches have very high overhead.  If that kind of absolute tracking is required than PIT(A) 
architecture is very robust.

John W. Colby

Reality is what refuses to go away
when you do not believe in it

On 3/7/2014 2:44 PM, Jim Dettman wrote:
>    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
>>
>
>


---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com