SQL: Link a table or use a constant in the application?

Discussion in 'OT Technology' started by Symphony, Jan 25, 2008.

  1. Symphony

    Symphony If you take the blue pill, the story ends.

    Joined:
    Jul 31, 2001
    Messages:
    25,335
    Likes Received:
    0
    Ok I'm doing some data mining stuff and currently am trying to figure out how someone else might have their tables laid out in an application. Ultimately, this is a minor issue, but I'm curious if someone else has thoughts on it.

    Basically, in one of the database fields is an integer, which is translated to a string when it is displayed.

    Would it be better to have that integer value link to another table which holds the string, or translate that integer into the string in the program (NOT though linking tables together...) before it is displayed?


    edit: and yes everywhere I said link I mean join :o
     
  2. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    Unless it's simply a boolean then I ALWAYS ALWAYS ALWAYS use foriegn keys and join with another table. This is required to meet the Second Normal Form. It also facilitates the use of Stored Procedures -- which can be a life saver when managing a few win32 and web-based apps that have to access the same underlying data.
     
  3. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    Your question is vague. Are the integers being converted into the string representations of themselves (i.e 12345 = "12345"), or are they being used as reference numbers for other strings (i.e. 12345 = "Hello Bob, how are you today")? If the integers just need to be converted to strings then you can do that in your code, but if the integers refer to other strings, then you should absolutely store it in a lookup table. It's not even about efficiency, it's about manageability. If any of those other strings have to change in the future, editing the lookup table takes a few seconds whereas recompiling the code could take hours.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Sample data makes things so much clearer...
     
  5. Dnepr

    Dnepr Guest

    What he said... ^^
     
  6. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    I'm not familiar with the "Second Normal Form". Then again, I'm not a DB admin. What is it?
     
  7. Pork Chop Sandwiches

    Pork Chop Sandwiches Extraordinary Member

    Joined:
    Feb 25, 2005
    Messages:
    7,217
    Likes Received:
    0
    Location:
    TGP
    .
     
  8. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    google.com
     

Share This Page