RE: Design Question

    Questions like this often turn into stinkers due to the way everyone sees the data, not the application. If your going to codify the values then the application needs to use the lookup table as a list of values which is much easier on the end user in many ways (cuts down on typing, errors, difference between MALE, male, Male, etc....) which then makes reports a lot more consistent. Of course app developers look at it as I'll just put the hardcoded values into the application as an answer, which holds until they've been gone for a year and no one knows how to update the application code (BTDT). Besides having primary and foreign keys makes joins perform as they should so there is no appreciable cost associated with their use. Also if your going to use lookup table my recommendation is to use a number as the primary key, populated from a sequence generator.  

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Balakrishnan, Muru Sent: Friday, October 16, 2009 12:58 PM
To: oracle-l_at_freelists.org
Subject: Design Question

Hello Experts,  

I request your opinion on, whether column values in large tables with more than million rows should be codified for better performance. For example a customer table could have following columns, gender and card type. In our team many prefer to spell out the entire word for every row (as Male, Female, Credit Card, Debit Card etc), but my recommendation is to codify (M, F, C, D, etc) with a lookup tables when needed. Their argument is, hardware is cheap including storage, helps readability and less joins. My argument is, production hardware is not cheap (we can buy 1TB for home under $100, but production hardware costs thousands), less overall blocks used improves performance, negligible problem with joining lookup tables. Please give your opinion.  

Gender

M vs Male

F vs Female  

Card type

C vs Credit Card

D vs Debit Card,

P vs Prepaid Card  

Thanks in advance,

Muru  

--
http://www.freelists.org/webpage/oracle-l