Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579

    Surrogate versus Natural key

    Do most Database Architects work in only one language (English, Spanish, German, Russian, Greek, Arabic, Hindi, Japanese) or at least think in only one language?

    The reason that I ask this question is that many people here on DBForums keep raising the idea of "use Natural Keys, they save table lookups" as though this idea made sense, when to me that is gibberish. The only way that I know to cope with multiple languages is to have some sort of surrogate key that links to a table that is directly releated to the concept, then have a one to many table to look up the associated verbage to go with the concept.

    Language is a presentation issue, but if you want consistant presentation then the language needs to be stored in a central place (like the associated database) so that it is the same on screens, reports, export files, etc. It can be handled as "one off" options each time you need it (so every report may refer to a thing by its own name), but at least to me that seems to be the road to madness.

    Am I missing something in terms of design, am I the only one that has to cope with multiple languages, or am I just further off base than normal this morning?

    -PatP

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    There is no hard and fast answer to this, there are only incorrect applications of the theory!

    How often have we seen threads where a user says they want to delete all duplicate records because they've simply assigned a surrogate key and thought that was enough?

    I have no beef with surrogate keys as long as a unique constraint is used with it! If you're using "lookup tables" with an integer surrogate key and a descriptor field; are you ever going to want to have the same descriptor more than once?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Pat - you are kidding surely? Are you really serious you want to start this thread?

    I like to go au naturel by default but I don't deal with web clouds and internationalisation issues and it is not a dogma for me.


    Quote Originally Posted by Pat Phelan
    The reason that I ask this question is that many people here on DBForums keep raising the idea of "use Natural Keys, they save table lookups" as though this idea made sense, when to me that is gibberish.
    Do you mean people assert that a db using natural keys requires less tables (i.e. no lookup tables) or that queries can be satisfied with less IO?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I think the implication is that you do not have to perform a join to retrieve the "lookup value".

    The same number of lookup tables will exist; how else do you enforce a foreign key constraint on the "offending" field?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I think the implication is that you do not have to perform a join to retrieve the "lookup value".

    The same number of lookup tables will exist; how else do you enforce a foreign key constraint on the "offending" field?
    If it is a "true" lookup table (i.e. no non-key values) and therefore not a true entry then you can enforce integrity with a CHECK constraint. I don't like to but I have seen it done.

    Assuming you're interpretation is right then yes that is correct - certain queries on dbs built using natural keys can result in less IO than if acting on a db built using surrpogate keys. Of course, the converse is true too, although this is more due to physical rather than logical issues
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump
    If it is a "true" lookup table (i.e. no non-key values) and therefore not a true entry then you can enforce integrity with a CHECK constraint.
    There is no such thing as a lookup table!

    This shall be my new mantra!
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I knew you would say that - that's why I used "quotes" Anyhoo - Pat meant IO so irrelevant to this (but not to the GOALS of agile development ).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    If you are not using a One True Lookup table, you are not being Agile enough. you want your developers sitting around making all sorts of lookup tables for State, "yes/no", and "true/false/FILE NOT FOUND"?


    oh, wait. I need to add this:

    ;-)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    ... many people here on DBForums keep raising the idea of "use Natural Keys, they save table lookups" as though this idea made sense, when to me that is gibberish.
    of course, a properly chosen collection of natural keys will simplify the whole language issue

    e.g.
    Men = Male (english)
    Fen = Female (english)
    Hfr = Homme (french)
    Ffr = Femme (french)
    Msp = Macho (spanish)
    Hsp = Hembra (spanish)
    etc.

    that way, you only need one "language-aware" gender entity table (notice i did not call it a lookup table)

    then you would simply use the correct natural key in the People table



    oh, wait, it has to be called the People_Persons_Personas_Leute table

    damn, pat, now i'm curious -- you go to pains to get your codes right, but what language do you use to name your tables? i bet you wuss out and use english, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Pat Phelan
    Do most Database Architects work in only one language (English, Spanish, German, Russian, Greek, Arabic, Hindi, Japanese)
    I've only ever built one multilingual system, this feature was added after the go-live and it consisted of simply replacing as much text as possible with icons and then having simple lookups for the titles and menus that remained. It catered for a bunch of European languages but, a year after introducing this feature, 99% of the users were still using English as their default language. It felt like a waste of effort at the time.

    Quote Originally Posted by MCrowley
    If you are not using a One True Lookup table, you are not being Agile enough
    Please bear in mind I'm exercising a great deal of self control not entering this debate

    Mike

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "one man enters, the other man leaves"

    oh wait, it was "two men enter, one man leaves"

    in any case, i'm now outta here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Rudy is correct, English is my native language, the agreed upon language used internally by all of the developers and staff, and the default language used when there isn't a localization for a word, phrase, or other text.

    My intent was to find out if I'm a "minority of one" in terms of looking at language, laws, units of measure etc as variables instead of constants. It appears that I'm the only one here that thinks the way that I do and sees a dozen languages as a normal thing, but I guess that doesn't surprise me.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Pat - are you making a general point about surrogates or specifically regarding language? And did you just chuck the "lookups" bit in there as an afterthought? I have nothing to say on the variables Vs constants side of things - that's a logical problem and I don't want to get into a discussion about the logical merits of surrogates and natural keys. I think I know what your main thrust is here but I don't know if you have a wider point to make too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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