[AccessD] Membership File Changes

A "meaningless" autonumber used as a PK is called a surrogate key.  It is a widely used strategy for 
a lot of reasons.

1) Multi-field PKs (the surrogate key alternative) ends up using much more resources, storage and 
computational, due to storage of the key in Foreign Keys in child tables (and grandchild / great 
grandchild etc).
2) Long integers are able to be compared using single instructions vs software based string 
comparisons (for text field multi-field PKs).
3) Multi-field PKs are often based on business rules, which can change over time, causing additional 
fields to be required to uniquely define the record, causing massive headaches in table maintenance 
(adding the fields to indexes, and adding additional fields to child FKs).  Contrast this with a 
surrogate key where the PK never ever changes, it is always just a (usually) auto-incrementing 
integer.  An integer in the parent table, an integer in the child table and NOTHING in the 
grandchild table.

This strategy is not without detractors however, mostly "old timers" from databases where 
multi-field PKs were ALWAYS used. Mostly they try to argue that it is possible to just read the PK 
down in the child / grandchild / great grandchild etc. simply by looking at the data in the 
multi-field PK, which "saves your bacon" when orphans creep in and so forth.  Additionally the 
"values" can often be pulled for reporting and the like, without requiring joins back up the chain.  
Both are true, but not (IMO) make up for the overhead and headaches that multi-field PKs bring with 
them.

The overhead and so forth just makes such constructs very unwieldy, and so, just as an example, I 
(personally and individually) pretty much NEVER use anything other than an autonumber surrogate PK.  
When I design a table, the very first thing I do is create the surrogate PK.  It is just "habit" 
(but intentional) and always works.

BE WARNED HOWEVER that using a surrogate key does not remove the responsibility of analyzing the 
data to determine what REALLY makes each record unique, and placing a unique index on that group of 
fields in order to prevent the same record from being entered multiple times.  A surrogate key HAS a 
unique index (by definition) but that index is not THE unique index required to prevent dupes from 
creeping into your database.  And be aware that yes, you will indeed need to do much more "joining" 
of tables to pull data from various levels in the tables.  There is no free lunch.  Surrogates are 
just (IMO) a much lower cost lunch.

John W. Colby

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

On 3/6/2014 7:15 PM, Jim Barrett wrote:
>   Just a note to publicly thank Stuart for sharing several messages with me to correct my ability to properly access records.
>
> The following changes were made to fix my ComboBox problem:
>
> File Definition
> 1)  The primary key indicators were removed from the LastName and FirstName fields in the File Definition and the AutoNum field was changed to the primary key.
>
> For the ComoboBox:
> 1) Row Source was changed from
>    - SELECT [TAGHSMembership].[LastName], [TAGHSMembership].[FirstName] FROM [TAGHSMembership];
> to
>
>     - SELECT TAGHSMembership.LastName, TAGHSMembership.FirstName, TAGHSMembership.AutoNum FROM TAGHSMembership ORDER BY TAGHSMembership.LastName, TAGHSMembership.FirstName;
>
> 2) Changed the  "Column Count" and "Bound Column" to "3".  That meant that the " value" of the combobox  was the AutoNum of the selected item.
> 3) Opened the After_update code and change:
>
>       - Me.RecordsetClone.FindFirst "[LastName] = '" & Me![Combo70] & "'"
> to
>       - Me.RecordsetClone.FindFirst "Autonum = " & Combo70
>
> Jim Barrett - Timpson, TX


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