Re: Normalisation versus optimisation

On 25 Oct 2001 07:16:40 -0700, edward.collier_at_techop.co.uk (Edward64) wrote:

>
>ProductType =
>ProductTypeID, (PRIMARY KEY)
>ProductTypeDescription
>
>ProductVariant =
>ProductVariantID, (PRIMARY KEY)
>ProductVariantDescription,
>ProductTypeID (FOREIGN KEY on ProductType TABLE)
>
>DespatchNumber =
>DespatchNumberID, (PRIMARY KEY)
>DespatchNumberDescription
>ProductVariantID (FOREIGN KEY on ProductVariant TABLE)
>
>My question is this. Should I include the following column in DespatchNumber table:
>
>ProductTypeID (FOREIGN KEY on ProductType TABLE)
>
>Is there any upside or downside to this? Normalisation versus optimisation.

The downside is easy: You are storing the same information in two different places. Thus you have to update both at the same time, in code (preferably in a trigger)
Also the people using the db might be confused, since they expect it to be normalized.

The upside I guess would be a small performance gain on SELECT's. I say 'guess', because I don't know if or how big a gain. Note that you place the optimization on the shoulders of the person writing the sql, instead of on the database itself.

-- 
Nis Jorgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks