Database Design and Reference Tables

  • Herb Overstreet-363572

    SSC Eights!

    Points: 886

    GENERIC! This alone should be a warning. What does generic mean? A replacement or different way of doing something and that something is usually at the expense of design and function. My Dad had two sayings he drilled into his kids, “You get what you pay for!" and “If you have time to do something, then take the time to do it right!” I am sure Dad would say to me (if he could from above) “Generic tables: Remember you get what you pay for. So you must not have time to do it right?” Generic reference tables for group of items very similar in nature might be okay where there's a code followed by the meaning, but with no repeating numeric values. Additionally, its contents would never change. But I cannot come up with an example. (I was thinking States and Countries, but the names keep changing in places like Africa).

    Just like drugs, generics don't work as well as the originals. I believe I took this generic approach in 1972 when I first started programming -- assembly language on a UNIVAC 1005, but changed a year later using FORTRAN and COBOL on a Honeywell 6000. Sorry generic don't work for reference tables just as those substitutes for that purple pill.

  • Robert Stewart

    Hall of Fame

    Points: 3399

    Look in the dictionary, and you might find "bad design" in the definition of generic.  Is it any wonder with people still designing garbage after having good design principles for so many years, that this kind of garbage design still pops up?  People seem to never learn.  They always have a newer, better way.  Guess what, it has been done before.  And that is why we have the design principles for normalizing a database that we have now.  IT DID NOT WORK!!!

  • Olga Smith

    Old Hand

    Points: 323

    My question to Steve is Why did he repost a bad article with clearly misleading information.  I would take it off the server.


  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Read it again... from a position where you need to create a bad design and that articles starts to make sens (which is what the writter intended iirc).



    I was reffering to this article which had about the same kind of response :

  • Robert Davis

    One Orange Chip

    Points: 28027

    Rarely have I disagreed more with an article I've read on here.

    Advantages of "GenericLookUp" Table

    1.We can store integer values to most of the data and have the description stored in the "GenericLookUp" table. As we know that performance is always going to be better when we deal with numeric columns than non numeric columns.

    Since when do you have to use a generic lookup table to have an integer key??? Your state lookup table should be an integer value as the primary key and then you could have a state abbreviation column and a state name column.

    State Lookup Table
    StateID StateAbbreviation StateName
    1 AL Alabama
    2 CA California
    3 TX Texas
    4 FL Florida

    Also, by forcing the extra dependency on a string column being required as a unique identifier for your lookups, every query that performs a lookup has an extra performance hit on it. Your primary key would have to be a composite key of intID and strCategory, which judging by your example table is clustered on strCategory first and intID second. Your PK index is now a huge index and index seeks are going to take a much longer time. And since the clustered index is huge, any subsequent non-clustered indexes on the table will be considerably bigger and slower as well.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069



    Hey this is the first time I post the 100th message on a thread .

  • Robert Stewart

    Hall of Fame

    Points: 3399

    Sounds like the new Access engine will help the guys out that want a poor design...

  • Mark Storey-Smith

    Mr or Mrs. 500

    Points: 596

    If there isn't a policy to stop 'really bad' articles being published on SQLServerCentral, there should at least be one that stops said articles appearing in the "One year ago..." section of the newsletter. Re-promoting these articles gives credence where absolutely none is due!

  • sushila


    Points: 35293

    For that the articles would have to be flagged in some way and/or the site-owners would have to read the comments & response posts to every single article..kinda monumental..that task...

    You could always post this in the "Suggestions" forum...I know for a fact that all suggestions are considered very seriously by Steve etc..

    **ASCII stupid question, get a stupid ANSI !!!**

  • Steve Williams-275942

    SSC Rookie

    Points: 25

    IMO having one physical table representing multiple logical tables is extremely poor design - I'm surprised a normally reputable/conservative site such as SQL Server Central would promote this idea

    Having one physical table per logical lookup table has benefits as follows:

    - Easier to reverse-engineer a data model from a physical database

    - Improved performance as database page hits are more likely to be on discrete pages for disparate tables (tiny as little tables like these are probably cached but every little bit helps)

    - Lends itself to minor divergence e.g. a specific lookup table has an extra column other than code/description pairs

    - Allows tables to have names that represent their function

    I've seen all 3 variants on this:

    1. 1 physical table per logical table

    2. 1 physical table for ALL logical tables but named views representing each logical table

    3. I physical table for ALL logical tables and you just have to know how to code it

    In my opinion these variants are listed above in decreasing order or desirability

  • danTheNoodleMan


    Points: 19

    Generic lookup tables may seem to be good solutions at first. It leads to complexity, rigidity of design, and lack of referential integrity to name a few. Please read this article

  • James Hurburgh

    SSC Journeyman

    Points: 78

    Glad to hear that other people thought this idea was horrible.

Viewing 12 posts - 91 through 102 (of 102 total)

You must be logged in to reply to this topic. Login to reply