Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Lookup design concepts

    I have a fellow developer advocating storing text rather than an ID. For example:

    I have a lookup table for a persons title, it has 2 columns:

    TitleID as integer (primary key)
    TitleDescription as varchar

    I would normally store the TitleID in the table that references it, which i would regard as standard practice

    He is saying i shouldnt store the ID but store the TitleDescription and that i wouldnt need to have an ID

    His argument is that it will perform better as there is no joins when querying

    Me and the fellow developers do not agree that this is a good design practice. I would appreciate your thoughts on this.

    Thanks

    Richard

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If you have surrogate keys already established, why would you not use them?
    I say store the ID.

    Rudy will probably chime in now and say store the natural key.

    So there you have your answer.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    It's really going to depend on a few factors. Are the text/varchar values relatively short (as compared to say a short story)? And do they change at all often? If these things are changing on a monthly basis, then definitely go with the Surrogate key. If you are looking at US State codes stored in a table with millions of rows, I would go with storing the character data in the million row table.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i was out all morning, otherwise i woulda chimed in sooner

    if you don't already have the surrogate keys established, your developer friend is right

    however, you should nevertheless still declare the Titles table for relational integrity porpoises!!!!!!!!!!1!!

    and you dasn't has to use a numeric id for it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I would appreciate your thoughts on this
    Assuming by title you mean Mr, Mrs, Miss, Dr etc then I'd go with storing the data as a simple character field. My reasoning would be:
    • Why complicate the data for no real advantage?
    • The data will be easier to read.
    • It will be just as fast as using an id, if not faster as you now don't need to join to another table.
    • The SQL code will be marginally simpler as fewer joins required. It doesn't sound like much but these savings soon add up across a whole system.
    • It uses just the same amount of space though who really worries about saving a few bytes space these days?


    It hardly seems a major point to arguing about though.

    Mike

    EDIT: though, as mentioned elsewhere, if you're already using the id field then you should continue using it.
    Last edited by mike_bike_kite; 01-13-09 at 16:30.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    There is no single best answer. Surrogate keys might save you some space in some cases but at the cost of having an extra index to update and more joins in your queries. So weigh up those kinds of factors and determine what is most beneficial.

    What's bad in my opinion is any blanket assumption that a surrogate key always should or always should not be used.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    if you don't already have the surrogate keys established, your developer friend is right
    Well I say his friend is wrong. Nyah.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jan 2009
    Posts
    4
    Thanks for all your replies. I must admit i have used surrogate keys as this is the way i have always done it. I have now researched Surrogate keys vs natural keys and from what i have read both are correct in the right situation. The issue i have is that some of the items can be quite long >50 letters and these items have nationally defined coded values. Also we have to import and export these values as the nationally defined codes. The likelihood of the lookup description changing is high. We also have lists defined by the users of the system and often they are misspelled, so correcting the data is easily corrected if it has a surrogate key. So i am still thinking surrogate keys are correct for my lookup lists.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    however, you should nevertheless still declare the Titles table for relational integrity porpoises!!!!!!!!!!1!!
    Ah yes - DKNF!
    Quote Originally Posted by pootle in his finest singing voice
    Are you Fabian? Are you Fabian? Are you Fabian in disguise? Are you Faaaaaab-eeeeeeeeeeee-aaaaaaaaaannnn in disguise?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dexon123@hotmail.
    The issue i have is that some of the items can be quite long >50 letters and these items have nationally defined coded values. Also we have to import and export these values as the nationally defined codes. The likelihood of the lookup description changing is high.
    Agree with you re surrogates then, although the bit in bold suggests a semi-intelligent key?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by dexon123@hotmail.
    The issue i have is that some of the items can be quite long >50 letters and these items have nationally defined coded values.
    So why not use the codes as a key? Assuming the code is a manageable size that sounds like a good plan. A nationally defined code is not a surrogate though.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I prefer using surrogate keys myself. But I think the important thing is to be consistent. I think it is poor style to use surrogate keys in some tables, and natural keys in others. To me, that is kind of like using multiple naming conventions.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    I think it is poor style to use surrogate keys in some tables, and natural keys in others.
    that's just plain ridiculous

    i have a very strong suspicion that you said it only to be a nasty, evil troll

    stop it, just stop it



    "A foolish consistency is the hobgoblin of little minds,
    adored by little statesmen and petty DBAs of all kinds"
    -- apologies to Ralph Waldo Emerson

    you would srsly use a surrogate key instead of the natural state code key in your states table? instead of the natural ISBN key in your books table? instead of the natural VIN key in your vehicles table?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by blindman
    I prefer using surrogate keys myself. But I think the important thing is to be consistent. I think it is poor style to use surrogate keys in some tables, and natural keys in others.
    Do you really value "style" more than practicality and performance? I don't think "style" is a good criteria for choosing keys. Naming conventions are a very different matter because they are purely concerned with presentation and comprehension. If a naming convention could have an adverse impact on performance or maintainability then it would be a good thing to break that convention.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    that's just plain ridiculous

    i have a very strong suspicion that you said it only to be a nasty, evil troll
    Them's fightin' words, you worthless piece of leder-hosen clad moose meat. I'll meet you halfway to settle this....which would be what? Wisconsin? Where the heck are you up there anyway? It's all like one big State to us. Like, the downstairs of your house has different rooms, but the attic is just one big space you occasionally have to visit when the roof is leaking...

    Take that!
    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
  •