Lookup Table Madness

Question

Lookup Table Madness

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

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

JJ B SSCertifiable Points: 5130 More actions · Answer 1

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.

Bruce Hendry Ten Centuries Points: 1244 More actions · Answer 2

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.

swninetails SSC Enthusiast Points: 158 More actions · Answer 3

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.

Frederick Goodrum SSC-Addicted Points: 428 More actions · Answer 4

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

manish chawla Newbie Points: 1 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

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)

DCPeterson SSCoach Points: 15266 More actions · Answer 7

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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