Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    7

    Basic design question: code & description tables

    Hello,

    First off, I apologize if this question is in the wrong section, but I see no "beginner" area.

    My question is relatively simple though: if I have many different codes/descriptions that my application needs, is it better to keep them all in one table, or split them into multiple ones?

    At my previous job, we had one large "code" table for fixed values. Each entry had an id, type, code, and description. We seperated when to use them via the type (for example, purchase order statuses would have a type 'poStat' etc.). At the job prior, we gave each type its own table.

    From what I've seen, the first is easier to model and maintain, but I assume that we take a performance hit from merging them all together.

    Is there any "best practice" guide to approaching this? Should I always keep them seperate tables, merge them when under a certain number, or just keep them together.

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Ah, the OLTP. One True Lookup Table.

    I've used both methods, and I've yet to decide which is better. Both have obvious drawbacks. The one from a proliferation of tables and the other from a proliferation of records.

    I guess if I had less than 10 lookup tables I would never consolidate them. Above 20 or so I would consider consolidating some of them, but I would be selective and make sure the lookup values conformed to uniform standards.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Blindman kind of mangled his acronym, OTLT is "One True Lookup Table" to me, but he has a good opinion.

    A lot depends on which database engine and development tools you are using, and how well the combination of them handle concurrency. If they don't play really well together, I'd strongly recommend separate tables for every lookup... It makes the locking issues a lot easier to handle.

    From a pure design perpective, every lookup class should have its own table. They are fundamentally different kinds of data (shoe colors and ski finishes might both be a number and a string, but they are still fundamentally different things).

    If you get past both of those considerations, you can consolidate the tables that have similar structures into a OTLT.

    I used to love OTLT, and normally had one in every database that I designed. I have now switched back, and almost now I have separate lookup tables for every different kind of thing that I lookup. I only combine lookups when I have more than one type of otherwise identical lookup in the same database.

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    OLTP? Where the heck did I get that? Online Transaction Processing?

    Missed my caffeine today...
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Please see this entry in my "blog" for my thoughts on OTLT!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Tony's OTLT blog post is one of four good articles that i have bookmarked --

    Dave's guide to the EAV

    One True Lookup Table by Joe Celko

    OTLT and EAV: the two big design mistakes all beginners make

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

  7. #7
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    i am with Pat. I used to use one table (which is a breach of correct normalisation), now I always use one separate Lookup table per class.

    I do not believe performance is a consideration in this issue, most modern SQL engines handle either one the same.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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