#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,031
    Rep Power
    535

    Good examples for using a natural key


    I've always struggled for identifying natural keys, and to date have only used them for the following:
    • Zip Code (i.e. 95431)
    • US State (i.e CA, NY, etc)
    • Timezone (i.e. America/Los_Angeles)
    • Some internal list used by your application that you want to enforce integrity (i.e. record status can be "active", "deleted", "pending")
    • Maybe system generated invoices (i.e. "5123-1231-424")
    • Probably not SSNs as one wouldn't want that data to be so engrained in the data (i.e.123-45-6789)


    What are other common natural keys which I should keep an eye out for when they become available?
    Last edited by NotionCommotion; February 7th, 2014 at 10:41 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by NotionCommotion
    What are other common natural keys which I should keep an eye out for when they become available?
    years
    believe it or not, i have on more than one occasion seen this --
    Code:
    id year
     1 1990
     2 1991
     3 1992
     4 1993
     5 1994
     6 1995 ...
    lat/lng
    should be obvious why

    keywords
    this one always starts a discussion, and if you don't understand both sides, please holler...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,031
    Rep Power
    535
    Originally Posted by r937
    years
    believe it or not, i have on more than one occasion seen this --

    lat/lng
    should be obvious why

    keywords
    this one always starts a discussion, and if you don't understand both sides, please holler...

    I was asking for good opportunities to use, not where they are misused. Was this your understanding of my question?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by NotionCommotion
    I was asking for good opportunities to use, not where they are misused. Was this your understanding of my question?
    sorry for the confusion, no, that was not my understanding

    years -- use as a good natural key... same for dates

    lat/lng -- use as a good natural key

    keywords -- use as a good natural key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,031
    Rep Power
    535
    Sorry, the confusion is me.

    For years, would this be for a table called "stuffThatHappendThisYear". I am not sure I understand.

    For lat/lng, would it be for a table called "stuffAboutThisLocation", and contain information like altitude, average temperature, etc, and maybe join to a table displaying temperature over time? Whould the PK look something like "-124.32412 +123.412313"? How do you deal with resolution (i.e. the number of digits?)

    For keywords, what else is in the table?

    Thanks
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,031
    Rep Power
    535
    Please ignore my remarks about years and lat/long. I am operating on just a few hours sleep, but get it now.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by NotionCommotion
    For keywords, what else is in the table?
    usually nothing!

    if you're having trouble visualizing this, take a few moments and consider a typical scenario --

    a many-to-many relationship between articles (newspaper, blog, whatever) and keywords

    each article can have many keywords, each keyword can apply to many articles

    how would you set up this scenario? what tables would you have, and what columns would those tables have?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,031
    Rep Power
    535
    Gotcha! All three of these keys fall into 4th general item:
    Some internal list used by your application that you want to enforce integrity (i.e. record status can be "active", "deleted", "pending")
    Any thoughts of natural keys for tables which contain multiple columns? Did you agree of my use of zip codes, US States, timezones, invoices, and SSNs?

IMN logo majestic logo threadwatch logo seochat tools logo