Re: Design Question
From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Fri, 16 Oct 2009 11:24:35 -0600
Message-ID: <4AD8AC53.1030306_at_optimaldba.com>
At what cost for performance? Are data integrity issues worth the potential performance improvement? How are they going to manage the integrity of the inputs? If at the application level, then the columns are open to non-application changes (or new-application changes that don't adhere to the standard). If it is using a check constraint, then changing the validation list is a column change (actually a column constraint change). Adding/changing a row in a table is simpler, more robust and just plain the right thing to do!
Date: Fri, 16 Oct 2009 11:24:35 -0600
Message-ID: <4AD8AC53.1030306_at_optimaldba.com>
At what cost for performance? Are data integrity issues worth the potential performance improvement? How are they going to manage the integrity of the inputs? If at the application level, then the columns are open to non-application changes (or new-application changes that don't adhere to the standard). If it is using a check constraint, then changing the validation list is a column change (actually a column constraint change). Adding/changing a row in a table is simpler, more robust and just plain the right thing to do!
Jared Still has written on the subject of normalizing for performance (http://jaredstill.com/downloads/normalize-for-performance.zip). I suggest reading this as a way to preparing a logical approach on this particular topic.
As for Male/Female, there are not just two options in some places. I don't recall if it was discussed on this list, but there are organizations with more than 5 gender classifications...
Regards,
Daniel Fink
-- Daniel Fink OptimalDBA http://www.optimaldba.com Oracle Blog http://optimaldba.blogspot.com Lost Data? http://www.ora600.be/ Balakrishnan, Muru wrote:Received on Fri Oct 16 2009 - 12:24:35 CDT
>
> 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