Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Does anyone use Common lookup tables?

    I am curious if this is a common practice and what the best practices are for maintaining referential integrity while using a common lookup table.

    I have always used a common lookup table wherever I have worked and now that we are replicating to multiple environments it makes maintenance much easier having to replicate only 1 lookup table that contains ~90% of our lookup data.

    Here is a decent description of what a common lookup table is:
    http://www.projectdmx.com/dbdesign/lookup.aspx

    Here is some sample DDL to help formulate a discussion on this topic:
    CREATE TABLE CODE
    (
    CODE_ID INT -- PK
    ,CODE_TYPE_ID INT
    ,CODE_NAME VARCHAR(250) -- LOOKUP TEXT
    )

    CREATE TABLE CITY
    (
    CITY_ID INT -- PK
    ,STATE_ID INT -- FK to CODE table of CODE_TYPE_ID = 1
    ,CITY_NAME VARCHAR(250)
    )

    Now obviously we could create a simple FK from CITY.STATE_ID to CODE.CODE_ID what we are lacking is constraining it to only the values with CODE_TYPE_ID = 1 (since this is a common lookup table we can store other values in the CODE table unrelated to States)

    I can think of two solutions to this, the first solution is to include a CODE_TYPE_ID in my CITY table and have a value of 1 in every single row (I know bad DB design), but this would allow me to create a compound FK that would match on both columns and this would work, albeit an ugly solution.

    Alternatively for solution #2 I could create an INSERT/UPDATE trigger on CITY to ensure the range of STATE_ID is exactly one of the values stored in my CODE table for CODE_TYPE_ID = 1.

    Any other solutions out there? How do others handle this problem?

  2. #2
    Join Date
    Jun 2005
    Posts
    319

    Common lookup table usage

    Solution #3, create a UDF + Check Constraint combo to enforce RI:
    Code:
    CREATE FUNCTION [dbo].[UDF_CHECK_CODE_ID](@CODE_ID INT, @CODE_TYPE_ID INT)
    RETURNS int
    AS 
    BEGIN
       DECLARE @retval int
       SELECT @retval = 0
       SELECT @retval = 
    (
    	SELECT 1
    	FROM CODE C
    	WHERE
    		C.CODE_ID = @CODE_ID
    	AND	C.CODE_TYPE_ID = @CODE_TYPE_ID
    )
    
       RETURN ISNULL(@retval, 0)
    END;
    Code:
    ALTER TABLE [dbo].[CITY]  WITH CHECK ADD  CONSTRAINT [CK_CITY_CODE] CHECK  (([dbo].[UDF_CHECK_CODE_ID]([STATE_ID], 1)>(0)))
    GO
    ALTER TABLE [dbo].[CITY] CHECK CONSTRAINT [CK_CITY_CODE]
    Last edited by Gagnon; 03-19-08 at 11:01.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Ah yes, we discuss OTLT every few months. There are a few folks that don't outright abhor it, and OTLT is quite appealing until the fifth night you are cursing it at 04:00... From that point on however, it quickly loses its luster!

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What you are referring to is know, somewhat mockingly, as an OLTP, or "One True Lookup Table":
    http://www.google.com/search?source=...=Google+Search
    It is a concept which seems neat and tidy, and which eventually every DBA stumbles across and thinks they have come up with an original idea, and yet it has many hidden and subtle drawbacks.
    Personally, I no longer even believe in lookup tables. See my post in this thread, which has lots of other informative posts as well:
    http://www.dbforums.com/showthread.php?t=1628045
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2007
    Posts
    62
    Quote Originally Posted by Gagnon
    Any other solutions out there? How do others handle this problem?
    Yes - do it properly with one table per type. Jeez. How hard is it? What are the downsides?

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Just look at the author's conclusion in the article that you linked.

    Quote Originally Posted by LINK
    Conclusion

    Whether it is used as a short term make shift solution or as a long term viable solution, common lookup tables have no place in sensible database design. While application enforced integrity is often touted by many in the developer crowd, the fact that DBMS is the centralized enforcer of all integrity constraints still stands. In that regard, considering preserving data integrity and logical correctness as the foremost goal in a given database design, common lookup tables are one of the worst kind of mistakes that one can make in a stable data management solution.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Jun 2005
    Posts
    319
    Pat - thanks for the link, I read the entirety of it, man the OP got flogged there. I am not saying he didn't deserve a lot of it

    blindman - by no means did I think this was a unique idea, I have seen it at old jobs and at my current job, to undo it would be quite the undertaking. Not so much the creating of 87 distinct lookup tables so much in having to refactor all of the procs, ASP and .Net code that currently utilize this table. I also read your post regarding there not being any true lookup tables anymore and I totally agree in a well designed database. One thing my company is shifting towards is removing a lot of business logic from the DB so these additional attributes (like "in progress" that you mention) will be encapsulated in the business component code. I am not so sure I agree with that line of thinking, but I am outnumbered at this point.

    Without opening up a whole new can of worms, I do have one question:

    Couldn't a poor man's RI be accomplished by creating a UDF to check against the range of values for the given "type" and then include a call to the UDF in a check constraint? I don't believe this would work if that column allowed nulls but otherwise it seems pretty solid in maintaining RI.

    One more quick thing to add, at the current firm I am at for every new project we work on there are probably about 5-10 requests for new lookup tables so part of the allure of using the OTLT is consolidating this static data in 1 table since this will likely get replicated to two other DB's (as compared to creating/maintaining 15-30 new tables per project (5-10 times 3)).
    Last edited by Gagnon; 03-19-08 at 00:55.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    What you are referring to is know, somewhat mockingly, as an OLTP, or "One True Lookup Table":
    What is it with you people?

    OLTP <> OTLT
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by Gagnon
    I read the entirety of it, man the OP got flogged there
    In return I've decided to make it my life's work to annoy the hell out them as much as I can

    Must admit you deserve a prize for getting to the end of that thread though.

    The fact you RI was your main concern indicates you shouldn't use OTLT. I usually use it simply because of the simplicity it lends to the overall design. I tend to see more issues with overly complex designs being used poorly by developers cause no-one can understand the DB and with the values in the lookups being years out of date cause the original designer left and no-one wrote screens to update these tables. Most people here seem to have more issues with bad data and so swing the other way. YMMV.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    I (the overpaid contractor who they pay for DB Design input) am still losing the argument against OTLT and EAV here at the current gig. Their employee DB professional sent me some mangled queries to tune yesterday on a database she is currently building with an OTLT design. And what do they have me doing instead of database design... C# and XML after I have repeatedly shown them the errors in their ways.

    Oh well. I am no no DB jihadist. If they want to keep paying me to clean up messes and put band aids on boo boos they create, I am fine as long as the checks keep clearing.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Quote Originally Posted by mike_bike_kite
    The fact you RI was your main concern indicates you shouldn't use OTLT.=
    sweet Jesus in a hottub. When is data integity not the number one concern?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by Thrasymachus
    I (the overpaid contractor who they pay for DB Design input)
    I preferred you when you were the suicidal philosopher living in a single room

    am still losing the argument against OTLT ... here at the current gig
    hopefully if you come up with a winning argument at work then you'll be kind enough to post it here.

    Mike

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Quote Originally Posted by mike_bike_kite
    I preferred you when you were the suicidal philosopher living in a single room

    hopefully if you come up with a winning argument at work then you'll be kind enough to post it here.

    Mike
    you can not win arguments against invincible ignorance with you or my boss.

    I have 2 rooms, plus a kitchen and a bathroom thank you very much and I am not suicidal, nor have I ever been because my life is not mine to take. That ultimate act of selfishness is a crime to all of those that you share your life with as well as any higher power you may or may not accept in your life.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump
    What is it with you people?

    OLTP <> OTLT
    Making fun of someone's dyslexia is bad enough, but mocking a blind man's dyslexia is really cruel.

    Quote Originally Posted by Gagnon
    One thing my company is shifting towards is removing a lot of business logic from the DB...
    Oh God. Which "business logic" are they removing? Data-based business logic or user-interaction business logic?
    How can developers be so stupid about this stuff?
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by blindman
    How can developers be so stupid about this stuff?
    You're implying that there is some kind of limit ???

    Developers get paid to develop code, so they value things that make it quick and easy to produce code.

    DBAs get paid to get the data correct, so they value things that enforce correctness even if that comes at the expense of a bit more coding time.

    Businesses get paid to do business. Businesses that succeed in the short run value developers that code quickly because that leads to shorter time to market. Businesses that succeed in the long run look at the big picture and consider the total cost of operations, so they value getting things done right the first time.

    Three different perspectives, three different sets of values, three different sets of behavior. Voila!

    -PatP

Posting Permissions

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