Re: Autonumber Fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thank you for the reply. I think the person (or people) who argue for a
"natural" PK have made the point that the PK does not need to be used in the
relationships. In other words, a unique field can be on the one side of a
one-to-many, and the PK can be a separate entity. The point of a PK, in
that way of thinking, is to guard against duplication (e.g. entering the
same person twice) in a way that would not be possible if an individual
field is set to Indexed (No Duplicates).
I would rather use a form's Before Update event for such checking. In
another posting I referred to the real-life situation of a mother and
daughter with the same name in a family-run business. Even if it is
uncommon, it is certainly at least possible. I would want the option of
allowing the apparent duplication in at least some cases. I would need to
reinvent the PK if I was using a multi-field PK for that purpose and
discovered an apparent duplication.
I am inclined to use autonumber or some other fixed value (e.g. Invoice
Number) as the PK. I use code to contrive an incrementing value in the case
of the aforementioned Invoice Number and other situations in which the
number is part of what the user sees. I don't see a need for two guaranteed
unique identifiers in one record. Even in the case of Employee ID numbers I
lean toward a meaningless number, because if the company changes hands they
may switch to a different numbering system for Employee IDs. However, I am
interested in hearing other views on the subject. In the course of the
dialogue I am learning some new things, even if I don't decide to change to
multi-field PKs as a matter of course.

"Amy Blankenship" <Amy_nospam@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23$CSZMIwFHA.2664@xxxxxxxxxxxxxxxxxxxxxxx
>
> "BruceM" <bamoob@xxxxxxxxxxxxxxxxxx> wrote in message
> news:eVYeEYGwFHA.2008@xxxxxxxxxxxxxxxxxxxxxxx
>> What would you do to guarantee uniqueness in a Contacts table or some
>> such involving names and addresses, in light of the fact that names and
>> addresses are subject to change?
>>
>> SQL underlies Access queries. The design grid is a sort of SQL GUI (as I
>> understand it). So I think you're saying that displayed order (e.g.
>> sorted by last name) is not what you are talking about when you talk
>> about physical order. If I understand, you are saying that the structure
>> of the index determines the order on the disk, not the order in the table
>> when it is viewed directly.
>>
>> I have a database that includes an Employees table. The primary key is
>> the EmployeeID. With it to do over again I might have used something
>> else, because it is at least possible that they will one day change the
>> format of EmployeeID, which is just a sequential 4-digit number.
>
> Which is why most people use completely meaningless Autonumber fields as
> primary keys. Because you can't change the value, format, or anything
> else of a field that is currently being used as a primary key. Also the
> autonumber field will usually have a smaller size (on disk, no less) than
> a more meaningful key. Therefore, if you are using it in a relationship
> or relationships, the other tables will have to store less information
> when they are referring to that primary key of this table.
>
> So, for instance, if you had an employeeID that was an autonumber, all of
> the other tables that refer to your EmployeeID would have saved 11 bytes
> every time they had a foreign key to your employee table, and you could
> have stored what is now your employeeID primary key just once, for a total
> of just the one 15 byte storage of the employeeID string. This is the
> whole point of normalization. Anything that is actually used as data
> should just be stored once, with the smallest possible reference to it
> from other places that need to relate to the base data.
>
> More than likely you'll eventually have to move to an Autonumber primary
> key there for the above listed reasons. Most of us encounter this
> situation at least once, and from that point forward we use Autonumber
> primary keys, since fixing the problem once it has developed is much more
> of a pain than preventing it.
>
> Hope this clarifies;
>
> -Amy
>


.

Relevant Pages

  • Re: Autonumbering Primary Keys
    ... For the primary key, ... autonumber as a candidate key. ... (SkillID, EmployeeID) is usually ...
    (microsoft.public.access.gettingstarted)
  • Re: Autonumber Fields
    ... > format of EmployeeID, which is just a sequential 4-digit number. ... Which is why most people use completely meaningless Autonumber fields as ... of a field that is currently being used as a primary key. ... More than likely you'll eventually have to move to an Autonumber primary key ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Autonumber Fields
    ... What are the dangers of using an autonumber field as the code for a code ... Certainly a persons name can never be a primary key - too many John Smith's ... >> format of EmployeeID, which is just a sequential 4-digit number. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Access 2003 Lookup Lists
    ... one record for each office location, with OfficeID primary key ... SchoolID Relates to tblSchool.SchoolID ... EmployeeID Relates to Employee.EmployeeID ... The subform will be in continuous view, so you can enter as many rows as there are people in the class. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Understanding Relationships
    ... so can be designated as the table's primary key. ... A column is functionally dependent on another column if for any value ... EmployeeID value 932 the values Grant and Smith are inferred for FirstName ... Table 2 [SalaryScale related to Table 3 SalaryScale] ...
    (microsoft.public.access.gettingstarted)