Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unanswered: To autonumber or not to autonumber...

    I have read a number DB articles and most seem to suggest that all non-link tables should have some type of autonumbering.
    I have a number of small tables (in ACCESS) containing data like:
    PRIORITY
    High
    Medium
    Low

    OPTION 1 - apply autonumber (making it the Primary key)
    PRIORITY
    1 High
    2 Medium
    3 Low
    Each row is now unique.
    But to ensure a 'duplicate' row like '4 High' is not added to the table I need to index (no duplicates) field 2. So what was the point in creating an autonumber Primary key?

    For small tables like the one above, should I bother with autonumbering just for the sake of it (and then indexing the second field anyway), or is there some basic design concept I have missed?

    Cheers,
    Damien

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: To autonumber or not to autonumber...

    Originally posted by Damien O'Donogh
    I have read a number DB articles and most seem to suggest that all non-link tables should have some type of autonumbering.
    I have a number of small tables (in ACCESS) containing data like:
    PRIORITY
    High
    Medium
    Low

    OPTION 1 - apply autonumber (making it the Primary key)
    PRIORITY
    1 High
    2 Medium
    3 Low
    Each row is now unique.
    But to ensure a 'duplicate' row like '4 High' is not added to the table I need to index (no duplicates) field 2. So what was the point in creating an autonumber Primary key?

    For small tables like the one above, should I bother with autonumbering just for the sake of it (and then indexing the second field anyway), or is there some basic design concept I have missed?

    Cheers,
    Damien
    The entire point of auto numbering is to creat a numaric primary key...why numaric?????? Numarics are easier and thus faster for a pc to interperet thus faster for a return in a joined query situation. If you plan to just join on the text string anyways then there is no need for an autonumbered column. if you want to join on it you will have to change your db structure adding the corispoinging numbered column in your base data as a forign key. it will be a bit of work, but a good design can save you hours of heart ache in the future.

    Regards
    Jim

  3. #3
    Join Date
    Jan 2003
    Posts
    4

    Post

    The purpose of the autonumber is to provide a way to insert a value into the record that uniquely differentiates it from any other record, and requires no input form the program or user. Being able to uniquely identify eahc record is a very important database design concept that many do not understand. Some even use a text field. I prefer a number field or a time stamp field because computers can count much better than they can spell, plus, in some situations, the DB engine will sort letters differently, depending on the platform and sort order. Number will never be sorted differently from computer to computer.
    In your particular situation it isn't as crucial as with a table where the values in all other fields, or just a majority, could be identical. I usually just use the autonumber when creating a lookup table because I don't normally care what number is assigned to the value. It is a relational design and the number just needs to be there, as a foreign key, but the value doesn't matter.

    In your case, you can also require unique values in other fields as well, where deemed necessary.

    Chris

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: To autonumber or not to autonumber...

    Previous respondents made very good points.

    There's also a logical reason why you want to use an autonumber to generate a primary key, independent of any information a user/developer may want to use.

    Over time, you may want to change the values in a table, or the structure of a table. However, an autonumber creates an independent primary key, that the database uses to relate between other tables.

    Take your example:

    High
    Medium
    Low

    You want some form of primary key, otherwise if you ever decided to change Medium to "Moderate" you would be stuck.

    So perhaps you assign your table as follows:

    H High
    M Medium
    L Low

    That will work, and you'll probably never have a problem. But what if some point you wanted to change High to Very High? Simple change, but now the code H really doesn't make sense with Very High... it should now be a V, but you're stuck with H (unless you want to go through a lot of work!).

    If you use autonumber, you could have a table with:

    1 H High
    2 M Medium
    3 L Low

    Put indexes on the character codes (H, M, L), and on the definitions (High, Medium, Low), and a developer can still use these as if they are primary keys... except the actual primary key is the numbers (1,2,3). Then over time, if you need to change the code H to V, you have no problems.

  5. #5
    Join Date
    Jan 2003
    Posts
    2
    I am now convinced.
    Thanks for the feedback.

Posting Permissions

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