Lookup Table Madness

  • I want to reply to the note posted above:

    "But I haven't been able to define, nor have I ever heard anyone else define, a useful generalized definition for "lookup tables"."

    For me it is like pornography: You may not be able to define it, but everyone knows what it is. And since I find the distinction of a lookup table, like the distinction of 'pornography' (vs art?), to be helpful, I don't mind that I don't have a definition that everyone likes. It just works for me/makes me happy, makes me more productive, and in my opinion, makes my database designs more user-friendly for anyone else who might come in some day and have to understand and manipulate that database.

    Not that I don't understand the above point too. I'm just offering a response.

  • I appreciate everyone's input. I find myself involved in more and more data exchanges, and translating another entity's "number to string" relationships between tables is always one of the first items addressed. It seems some "Technically Correct" term of the day is in order to aid in discussions when categorizing those tables that hold nothing more than support data for the tables that contain the meat of the system. Thanks again, enjoyed the different perspectives.

  • Completely Agree, there is a time and place for all designs and no one way to create an architecture. Arguments can be made about triggers, constraints, defaults, keys and lookup data sets. Nothing irritates me more than a close minded developers who believe school taught him everything he will ever need to know and experience is outdated and so the only way to create is the way their education taught them. I agree the article made good points on design consideration but was far to one sided to be valid.

  • At the risk of firing up this age old debate. I would like to propose a different perspective on these arguements. I suggest this because there are some like myself who listen to both sides and can get stuck in gridlock. My perpective is as both a programmer and business owner.

    I don't care who gets stuck with the extra work. My DBA, my designer, or my programmer. The work needs to get done. When stuck with two competing concepts, I think the answer lies in cost.

    Tell me, which approach can I afford to implement?


    Kindest Regards,

    Frederick Goodrum

  • I have 10 different tables holding different set of transaction data(no relation at all). Just to standardize the data for reporting i have introduced key columns and loaded the data in one table and now this table is having 500 columns with 1 primary key clustered index on identity column

    On top of it, i build a processing engine(10 sps) which pulls data from this single table (only 50 cols max at one time). Do you think if there is any performance issue

  • manish chawla wrote:

    I have 10 different tables holding different set of transaction data(no relation at all). Just to standardize the data for reporting i have introduced key columns and loaded the data in one table and now this table is having 500 columns with 1 primary key clustered index on identity column

    On top of it, i build a processing engine(10 sps) which pulls data from this single table (only 50 cols max at one time). Do you think if there is any performance issue

     

    Heh... is a frog's butt water-tight? 😀

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • If I understand your solution, yes I would expect to see performance problems. Far worse, you're going to have data integrity issues. Even if you contrive to load and manage the data well, future programmers/administrators will have difficulty making sense of it.

    What possible benefit do you anticipate by combining the data from ten different and unrelated tables into one?

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • For anyone trying to get to the original article, here is the link: https://www.sqlservercentral.com/articles/lookup-table-madness

     

Viewing 8 posts - 196 through 203 (of 203 total)

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