Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoNumber Revisited

    I was perusing some of Charlotte's posts and found one from Jan 26 about AutoNumbers in which she said the following:
    "I personally object to using data as a primary key. A PK should be nothing but a PK rather than meaninful [sic] data".

    I can somewhat agree with the second sentence, although my experience has been during the past 20 years that people do make the key data quite meaningful because they need to remember a lot of them (or at least they used to).

    Being from the School of Legacy Databases, I supposed that an AutoNumber was strictly designed to save you the trouble of inventing a key value, something we used to do in the past (sometimes with some difficulty).

    Fer example:
    CustID(Key)........Name
    USGOVT.............US Government
    ACMEF1.............Acme FireWorks Company (1 for Canada)
    ACMEF2.............Acme FireWorks Company (2 for US)

    The table would be indexed on CustID and if we needed to enter the CustID in an OrderEntry form, most users would remember the most familiar ones and I (only I) would have to look them up. [img]/w3timages/icons/alien.gif[/img]

    Now if Access assigns an AutoNumber key value, is it not much more difficult to enter foreign key data in fields in related tables as you more than likely need to do a lookup by name for each and every customer?

    Even if you use a Combobox (with 5000 AutoNumbers) for lookup, you'd like to get at least somewhere in the neighbourhood of the required customer, I should think.
    Customer names in a cboBox aren't always that helpful either without a city or some other tag that differentiates all the John Smiths?

    I haven't used an AutoNumber field as yet (and wasn't planning on it either).

    Is my brain stuck on some legacy design ideas?
    [baby with long grey beard]

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber Revisited

    Having autonumber keys have a couple of advantages over alphanumeric keys.
    - You do not have to bother about a unique key, autonumber keys are always unique
    - Searching numeric fields is always faster compared to searching alphanumeric keys.
    - A numeric index is always faster than an alphanumeric indec.

    You can allways define a foreign alphanumeric key (do not forget to put an index on it!) to ensure a search facility on an alphanumeric field.

    Personally I allways use meaningless autonumber keys for all of my tables. I developed a wizard that generates a selection form, a detail form and all the code (a class module with database access to the table and a normal module with the form and control handling) based on the table definition. Working like this ensures a consistent design and a uniform user interface.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoNumber Revisited

    The real secret to using AutoNumbers as primary keys is that you don't have the user enter them or usually even see them. Instead, you give them a combobox or listbox with meaningful information they can pick from.

    So, instead of trying to remember the key for a customer, you let them pick the customer by name. When you have a very large number of items, you can use an unbound text box so that they can enter the "criteria" (i.e., the first few letters of the name) and use the afterupdate event of the textbox to build the rowsource for the combobox so that you only display items that match the criteria. That narrows the choices down to a workable number. If your customer is Acme FireWorks Company (Canada), that's what you display in the dropdown. You'll have far fewer mistakes in data entry and life will be a lot easier for new employees who haven't memorized customer ids yet.

    If you really feel you must have alpha-numeric keys to select for things like customer numbers, go ahead and use them, but don't make them a primary key. Make them a unique key in a customer table to keep out duplicates, but use an autonumber as the primary key and insert it into related records instead of the alpha-numeric key. Remember, good design hides the tables from the users, so all they need to see is a form or query or report that uses a join to display the alpha-numeric key based on the foreign key in the record. You can build a combobox with a hidden first column that contains the autonumber key and display the alpha-numeric key and the customer name in the other two columns. Then if the user enters the alpha-numeric customerID, the actual PK will be entered into the record.

    Take it from someone who has heard over and over that "we're never going to change that so it's OK to use it as a key" and then been told, "Oh, by the way, we changed the employee number from a 5 to a 7-digit number, OK?" Yeah, right. If you use an autonumber underneath it all, they can change it to anything they want and it won't affect the data at all.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber Revisited

    Also don't forget: should you use replication you will lose all control over the Autonumber sequence. Access will assure no duplications by wildly changing the scheme. I only needed one test to realize the trouble I was in. I was using Autonumber to assign unique Contract Numbers. My table had happily grown record by record from fldContrID 1000 through 12278 with the occasional lost number. On adding replication, the next fldContrID was <font color=red>981037275</font color=red> [img]/w3timages/icons/yikes.gif[/img] Try to remember that one, oh yeah US Fireworks, I remember customer number 981037275. By the way, like Charlotte says, never assume. If you decide you'll never used replication, someone, sometime will come up with a critical reason why you need to!

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber Revisited

    Thanks Charlotte [img]/w3timages/icons/love.gif[/img]...now that you put it that way!

    Luckily I have only made four tables sofar with one record in each. I think I am going to my workshop right now and tinker with AutoNumbers a bit. [img]/w3timages/icons/devil.gif[/img]

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoNumber Revisited

    Have fun but try not to have any parts left over when you're through![img]/w3timages/icons/laugh.gif[/img]
    Charlotte

  7. #7
    TomTn
    Guest

    Re: AutoNumber Revisited

    I'm very interested in your Wizard that creates forms. Can you give more info or a copy?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •