Re: Normalisation versus optimisation

"Edward64" <edward.collier_at_techop.co.uk> wrote in message news:74bed22a.0110250616.7887c6d7_at_posting.google.com...
> Three tables: ProductType, ProductVariant and DespatchNumber.
>
> They enjoy a 1:n relationship, left to right e.g.
>
> ProductType -> ProductVariant, ProductVariant -> DespatchNumber
>
> The design of the tables is simple enough:
>
> 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 target database is SLQ Server 7.0
>
> TIA
>
> Edward

You won't necessarily get a performance gain by denormalizing as you suggest.

It depends on a lot of other factors, such as indexing/clustering strategies, update/read ratios, number of items at each level, etc.

The best advice is to do it 'properly' and then optimize later if it really does trun out to be required. Received on Mon Oct 29 2001 - 10:37:10 CET