Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Unanswered: PK for Lookup Tables?

    Our DBA just suggested, "Instead of using business key for Primary-FK relationship we can create add identity column for each existing lookup table and make it as a PK. In the long run it makes the application more robust in case if any changes to a business key."

    My thought is this can be true for some cases but not for all cases. For example, for State lookup. I'd rather use "VA" instead of an integer to join our Contact table to the State lookup table make it more intuitive when querying the table directly, instead of always joining with every lookup table, which we have about 5 or more lookups per table.

    It will take a week or so to redo the table PK/FK constraints, redo the transactional application, and of course unit test. Therefore, I need to know if this is definitely just a matter of preference or is this the general best practice? What are advantages and disadvantages?

    I couldn't find SQL Server articles if this is the best practice. I tried googling but didn't find any direct answers

    Thanks in advance for your help.

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    I agree with your dba. Are you saying you have a table called STATE that just has a single column of 'AK','AR','AZ', etc? The goal is not for you to use 18 instead of "VA", but for the database to. The database is designed exactly for that, and it can do it very quickly and efficiently.

    You will still use "VA," but you will just specify the value on the key table instead:

    Code:
    select sum(sales) from placements as pl inner join state as st on pl.stateID=st.ID
    where st.value='VA'
    If your lookup / key table has millions of records - the indexes on these keys can be the different between waiting hours and waiting minutes for an answer.
    Kit Lemmonds

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    We have a standard model for Lookup Tables, we call them Code Tables here.
    They have an Id int, Code a CHAR(1-10), ShortDescription a VARCHAR(25 -100) and a LongDescription a VARCHAR(255).
    In drop down selection lists you can show both the Code and the ShortDescription.
    The LongDescription is most of the time only used for titles in reports.

    In your "VA" example, this could be the values
    17 VA Virginia State of Virginia
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2011
    Posts
    3
    Currently we have 165 lookup tables with 458 joins that needs to be implemented against these 165 lookup tables. They are not that big, the most is 4100 records. Also, we are building a new transactional application and migrating these lookup tables from a legacy system, which means they have already chosen their primary key. And our technical users will want to have a front-end to update these lookup tables since they are driving the data.

    From what I'm hearing, there are 2 reasons for using identity key of type integer for lookup tables.
    1. For when business rules change and they need to reuse the same code.
    >>> However, I'd say 80% of them will remain the same codes.
    2. For indexing
    >>>Question: Is indexing important in transactional systems?

    Thanks.
    Last edited by morf; 07-08-11 at 17:20. Reason: corrected #s

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Agree with your DBA. Blindman's Mantra: "There is no such thing as a lookup table."
    Design your databases to be robust from the start.
    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 morf View Post
    I'd rather use "VA" instead of an integer to join our Contact table to the State lookup table...
    ah, the sweet voice of reason, revealing an actual intelligent person, rather than a puppet

    what are the odds that virginia is ever gonna change VA to something else?

    and yet the consistency-matters-more-than-common-sense dba stormtroopers would have you substitute an integer, and then force all your address lookups to use an extra join to find out that 17 actually means VA

    don't listen to these guys, they are simply trying to overcomplicate things, bolster their feeble database design rules (even when doing so is clearly unnecessary and inefficient), and ensure their own job security

    that emperor don't gots no clothes, baby
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    What are the odds that the city of Juba will be in a different country than Sudan? Tomorrow it will be in Southern Sudan.

    Roac's mantra: Never make any assumption. The time you assume something, that's a weak point.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Nov 2003
    Posts
    167
    In general I always prefer surrogate (generated) keys, but especially when there is a front-end application. It just makes things simpler.

    Let's say your front-end has a control for the user to select a state. This control is populated with a procedure, state_lst, which returns the ID and value of every state. The control only shows the value ("VA," "AR", etc), but when they select it, the front-end actually throws the indexed ID at the database - resulting in faster processing. Inserting or updating values into these tables will take slightly longer - but nowhere near as long as if you are using the actual values ("VA," "AR," etc) as the primary key since all references will have to be updated.

    If this system is being migrated into a newer system that will already assign them a surrogate key - then the benefit may not be worth the work. If there's any chance at all that this migration will be postponed or cancelled, I'd key the system.

    There's another post on the board right now about natural vs surrogate keys. It's one of those old debates that dba's argue about instead of sports. I think that post is more about those gigantic natural keys that attempt to store every field in a single key (god key?), but it's related. Here's a link:

    http://www.dbforums.com/microsoft-sq...-question.html
    Kit Lemmonds

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    what are the odds that virginia is ever gonna change VA to something else?
    You and I both know that that is not the significant benefit of natural keys, so no "straw man" arguments please.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    You and I both know that that is not the significant benefit of natural keys
    how do you know what i know? speak for yourself

    anyhow, yeah -- the significant benefit is that you don't have to perform the extra join

    oh, and uniqueness, that's a plus, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    In my country, the postal services had assigned a unique postal code to each and every village. Those postal codes had been there unchanged for 50? - 75? - 100? years. So most database systems had the Postal code as PK for the Village table.

    Then came the 21th century, economical crisis and a new automated routing system to find the most efficient post rounds. Many villages received a new postal code, some postal codes were now used for other villages, some villages now had multiple postal codes, ...

    Never use a natural key that you don't control, as a PK. Use a surrogate key and make the natural (candidate) key an attribute that you can change whenever needed.
    don't listen to these guys, they are simply trying to overcomplicate things, bolster their feeble database design rules (even when doing so is clearly unnecessary and inefficient), and ensure their own job security
    Right, and I did not have sexual relations with that woman, Miss Lewinsky.
    Last edited by Wim; 07-09-11 at 20:49.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim View Post
    So most database systems had the Postal code as PK for the Village table.
    that's pretty silly, when the village name would've been a better natural key -- and no, i'm not really keen to hear that there might be two villages with the same name, and how the postal code solves that problem (it doesn't)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by r937 View Post
    that's pretty silly, when the village name would've been a better natural key -- and no, i'm not really keen to hear that there might be two villages with the same name, and how the postal code solves that problem (it doesn't)
    Actually, we do have a few villages (2 names, 4 villages) with the same name, and with 4 different postal codes (that's how they solved it). So using the village name as a natural key would have been impossible, let alone a good idea.

    Because in another rationalisation round, the number of villages has been reduced severely, saving out on a lot of municipal officials, town halls, ... So lots of villages now got another name (the name of the one absorbing them), yet they kept their old postal code. In this case the use of the postal code as PK didn't force those companies to rework their systems, they only needed to update the village name in the lookup table.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    The city name cannot be a natural key unless the city name is the only thing you care for. Natural keys are candidate keys, and as such they are required to be unique, so if you care for which city you'll need multiple rows with the same name, and something else to make it unique. Zip code is something you can use as a natural key instead, but personally I prefer to have a surrogate key as primary key, and @Wim has pretty clearly described why.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    how do you know what i know? speak for yourself
    As an American, I reserve the right to tell Canadians what they do or do not know. This is not arrogance. Rather, I just figure you don't get good internet access way up there.

    Quote Originally Posted by r937 View Post
    oh, and uniqueness, that's a plus, too
    You are conflating keys with constraints. Having a defined surrogate does not prevent you from enforcing a unique natural constraint. Moot.
    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
  •