Re: Normalisation versus optimisation

> Three tables: ProductType, ProductVariant and DespatchNumber.
> 1:n relationship, left to right e.g.
>
> 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)
>
> 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.

If ProductType is included in T_DespatchNumber... Adv: Higher performance, can resolve ProductType faster. Dis: Lower data integrity. You, not the db engine, are responsible for keeping the data synchronized.

In an object-oriented database, it could be modeled as below:

ProdType1
  .Descr
  .Variants

     Var1
       .Descr
       .DespatchNos
          DespatchNo1
            .Descr
          DespatchNo2
          DespatchNoN
     Var2
     VarN

Because XDb is fast and flexible, denormalization is rarely necessary. Download your free copy from http://www.xdb1.com Received on Fri Oct 26 2001 - 05:04:43 CEST