Re: Design Question
Date: Sat, 17 Oct 2009 14:53:29 -0500
I was once involved in this sort of operation for a major medical software company. Many of the advantages and disadvantages have already been mentioned. Another advantage of a single table is that you can restrict changes and set up a server piece on the application server with the task of populating the code values on the server side without even going to the database.
There are other some things to keep in mind, like you might want to identify the set of values that the encoded values belong to in your decode table (gender, credit card type) for example, and you might have local and universal display values.
On Sat, Oct 17, 2009 at 2:30 PM, Jay Kash <jaykash_at_hotmail.com> wrote:
> Tuning for performance is often feasible, but correcting bad data can get
> really messy.
> What good is bad data that performs pretty well!
> From: "Nuno Souto" <dbvision_at_iinet.net.au>
> Sent: Friday, October 16, 2009 7:12 PM
> Cc: <oracle-l_at_freelists.org>
> Subject: Re: Design Question
> Balakrishnan, Muru wrote,on my timestamp of 17/10/2009 2:58 AM:
>> 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.
>> Completely in agreement. Denormalization might save joins but I have yet
>> to see a case where it saved on data.
>> In fact, the opposite is generally the case: it greatly increases the
>> amount of data that needs to be stored and therefore the amount of I/O used
>> to manage it.
>> If that increase conterbalances any perceived or actual overhead of joins
>> is wide open for debate and there is no final answer: each case has to be
>> examined on its own conditions.
>> Normalization was not "invented" to save disk space. It was initially
>> intended to save the amount of I/O one has to perform to manage or retrieve
>> any given information.
>> "Amount of I/O" is not the same as "disk space" and I know for sure which
>> one causes performance problems.
>> Nuno Souto
>> in sunny Sydney, Australia
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Sat Oct 17 2009 - 14:53:29 CDT