Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    4

    When to use numeric IDs as Primary Keys

    Hi ... I had a basic concepts question ...

    I have seen some tables which have a numeric ID as the Primary Key.
    This a running number with no "intelligence" ...

    I am trying to see when to use this ID as the PK vs actual data fields to form the PK.

    I could think of the following ... I am looking forward to hearing from the experts.

    Use Numeric ID when
    a) number of records in a table are really large - increase performace.
    for e.g. Invoices or Receipts table in an ERP app.
    b) when your base identifier could change
    For e.g.
    PK username (jdoe)
    firstname (Jane)
    lastname (Doe)

    In this case it might be a good idea to have
    PK user_id
    username (Candidate Key)
    first name
    last name

    In this case if Jane's last name changes, then it does not affect the PK and if I have audits, then I am fine ...

    Another example could be Item.

    Rest of the time use actual fields to form the PK.

    for e.g.
    PK COUNTRY_CODE
    PK STATE_CODE
    STATE_DESC ...

    Any thoughts?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have a very good understanding of the pros and cons
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    increased performance?
    George
    Home | Blog

  4. #4
    Join Date
    May 2008
    Posts
    4
    Hi ... Thanks a lot for your replies ...

    George ... by performance I meant ...
    I could have the following struc

    Table = Project Instance
    PK Project Code
    PK Year
    PK Quarter
    PK Month
    ...

    OR
    Table = Project Instance
    PK Project Instance Number (Running Number)
    Project Code
    Year
    Quarter
    Month
    ....

    In this case I'll a Candidate Key consisting of (Project Code + Year + Quarter + Month) ...

    If Project Instance is a table which is being used as a FK in a lot of other tables, then I am better of using just one field as the PK.

    Is this a correct assumption?

    Also is it true that is faster to search numbers vs strings?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Rudy favors natural keys.
    I, and the other right-thinking star-bellied sneeches prefer surrogate keys.
    I would encourage you to be consistent either way, and not mix methodologies within a database. Unless you like aggravating your developers.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by popeye1974
    If Project Instance is a table which is being used as a FK in a lot of other tables, then I am better of using just one field as the PK.

    Is this a correct assumption?
    yes, that is correct, that's a very good example of when a surrogate key would be advisable





    blindman, what's wrong with "mixing methodologies within a database"

    surely you wouldn't assign a surrogate key to state codes just because there are other tables in the same database with surrogate keys?

    come on, man -- the slavish adherence to silly rules is worse practice than using a natural key when it's appropriate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm with Blindman both on my preference for PK values that the user neither sees nor understands, and on the point of avoiding mixing methodologies too.

    My experience has always been that if the user can see it, they will eventually want to change it. Many of the products that I work with are hybrid technologies, some parts implemented using one tool, some parts using a different tool. In these environments, propagating changes to primary/foreign keys are ugly at their very best, and those changes are rarely examples of the products behaving at their best!

    With that being said, I also firmly believe that alternate keys should be declared (using UNIQUE constraints) so that the natural keys are also represented and preserved. While practical experience has made me very gun shy about using natural keys as primary keys, that means that I use a surrogate in place of (to represent) a natural key and it emphatically does NOT mean that I disregard the natural key...

    My reason for avoiding mixes of natural/foreign keys is more selfish... I tend to develop libraries of code instead of re-inventing the wheel every time I need a ride. My libraries tend to "think" in terms of widgets that have widgetID columns. This means that it is a LOT less work for me to use a schema that is consistent than a schema that isn't consistent.

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    My libraries tend to "think" in terms of widgets that have widgetID columns. This means that it is a LOT less work for me to use a schema that is consistent than a schema that isn't consistent.
    consistency, let me show u it

    create table addresses
    ( ...
    , stateID char(2)
    );
    create table states
    ( stateID char(2) not null primary key
    , statename varchar(37) not null unique
    );

    vwalah, 100% consistent with "widgets that have widgetID columns"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    blindman, what's wrong with "mixing methodologies within a database"

    surely you wouldn't assign a surrogate key to state codes just because there are other tables in the same database with surrogate keys?
    Yes, I would. And we already had this discussion. You remember my new mantra, don't you? "There's no such thing as a lookup table."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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