Re: Performance problem

terryg8 wrote:
> > small lookup tables are very useful. You don't want to store, say,
> > "Degeneral Motors", "Moobma Industries", etc similar strings in a table
> > with 43 million rows. Better have a small ( ~ 1000 rows )
> > lookup table with these strings and join it with big table
> > when needed.
> >
>
> My opinion is that codes are old fashioned and arose from a time
> when space was at a premium. Store the actual values. Disk is cheap
> and codes just hide information. Use the 1000 row table as a
> verification
> table. Obviously, there will be exceptions when codes are needed i.e.
> legacy
> systems etc.
> My 2 cents worth.
> TRG
I disagree heartily! If for no other reason that storing a description into a record rather than some sort of foreign key representation can denormalize your table(s). Say you have a 10,000 row table of orders spanning some number of years. Some subset of these orders were placed by Company X. However, in between Year 1 (when the table was first established) and now, Company X changed its name. By creating a *SMALL* lookup table that contains ONLY the company information and a code identifying that company, and then storing that code in my orders table as a foreign key I can still query for total orders by Company X regardless of what its name was or is. However, if I stored the actual name in the orders table I'd have to remember that I need to select for "Company X", "Company X and Sons", AND "Company X International" to get all the data for what is, in the final analysis a single entity.

Ditto if I need to know who the contact person is at Company X. I can store that information in the COMPANY table and still get the right name even if the order I'm referring to is from some time ago.

Admittedly, you have to analyze your data requirements and do preliminary normalization of your table structures (and should do so) before you can make any hard and fast rules regarding "Codes" versus "Descriptions". But, keep in mind, that data -- especially with regards to human organizations -- will never be static. Received on Tue Oct 07 1997 - 00:00:00 CDT