Re: Normalizing vs. Denormalizing

In article <3157ADF8.289_at_mail.esrin.esa.it> Kevin Johnson Serco DPE/IO <johnson_at_mail.esrin.esa.it> writes:
>Suu Quan wrote:
>>
>> :comp.databases.oracle / Will Kooiman <wkooiman_at_csac.com> / 2:58 pm Mar 20, 1996 /
>> >a normalized design will outperform a denomalized design.
>>
>> I experienced the exact opposite.
>>
>
>"Horses for courses"
>i.e. Your design should be optimised for the performance to the transactions/queries
> you will actually perform.
>
>I am sure that your experiences are both true but represent experience of
>different types of usage.
>
>At the risk of over-generalising :
> - a normalised design is probably better where you have many UPDATES

Please explain this to me. I would think if you are normalized, you would have the updates spread over many different rows, which would be much slower than a highly denormalized physical design with everything jammed into one record.

Let's take a simple bill example. You have a customer, all his info, and a bunch of line items, each with a product, amount, price. You want to give this customer 10% more of everything, because, hey, he's a good customer. So the update becomes, get all the children belonging to this customer. You could have up to a disk access per line item, or more! If all the lines are denormalized into an array in one record per bill, it merely becomes core-bound.

Codd may have proven that it is not necessary that there be better performance in a normalized implementation, but it's still been my experience that it is best to normalize the logical design, then denormalize the critical parts of the physical implementation for performance. Even in hierarchical databases! So please explain.

> - a denormalised design is probably better where you have many QUERIES
>

It really depends whether your queries follow the design. If you've designed the warehouse version of the above bill as arrayed, and all the queries are based on product, you're doomed as doomed can be by not normalizing.

>You need to decide which types of transaction/query you want to optimise
>for, before deciding to NORMALISE or DE-NORMALISE.

Given the fact that over time requirements evolve, normalize, and denormalize only if there is a clear performance requirement for it. Or don't use Oracle if no one has the relational basics.

If you are designing tight realtime systems, all bets are off.

jg

>
>Kevin
> ______________________________________________________________________________
> | / _ o _ | _ | _ _ _ _ _ EMAIL : johnson_at_mail.esrin.esa.it
> || /_|\ /||/ | |/ \|/ ||/ ||_ / \|/ | PHONE : (+39) 6 941 80 633
> | \\__ \/ || | \_/\_/| || |__|\_/| | ADDRESS : European Space Agency
> Via Galileo Galilei
> Information Administrator Frascati 00044 ITALIA.

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O