RE: Design Question
Date: Fri, 16 Oct 2009 14:10:54 -0400
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.
Senior Oracle DBA/NA Team Lead
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Balakrishnan, Muru Sent: Friday, October 16, 2009 12:58 PM
Subject: Design Question
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.
M vs Male
F vs Female
C vs Credit Card
D vs Debit Card,
P vs Prepaid Card
Thanks in advance,
MuruReceived on Fri Oct 16 2009 - 13:10:54 CDT