[AccessD] Membership File Changes

Arthur Fuller fuller.artful at gmail.com
Fri Mar 7 10:11:04 CST 2014


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


More information about the AccessD mailing list