[AccessD] Mucking around

Jim,

When you are dealing with data marts/warehouses,
most normalization rules go out the window.

A fact table is highly normalized. A dimension
table is highly denormalized. It is simply how
they work and how they are correctly designed.

For instance in a customer dimension table, you
would store all the information about the
customer. You would denormalize it to put the
address and phone information in the same table with
the customer name. The same with the shipping
terms for the customer. Essentially, all the
information that you might ever want about a
customer will be in the one table.

The dimension tables are then linked to the fact
table with the PK. The fact table is extremely
specific data at a specific granularity. For
example, sales by customer for a specific product
on a weekly basis. The weekly is the granularity
of the fact. There would be at least 3 columns
in the PK of this fact table, customer ID, product
ID, and Date ID. The last one would be used to
identify the week the sales occurred in.

If the granularity of the fact is the same for
your data, having a Entry type of actual or
trial as part of the fact is perfectly fine.
It has to be part of the PK for the fact.

Robert

At 11:54 PM 9/27/2007, you wrote:
>Date: Thu, 27 Sep 2007 15:55:55 -0500
>From: "Hale, Jim" <Jim.Hale at FleetPride.com>
>Subject: Re: [AccessD] Mucking around
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID:
>         <FBEA4C39D7934A47A057CDFD3CF070EF4CAED7 at corp-es01.fleetpride.local>
>Content-Type: text/plain;       charset=us-ascii
>
>
>But is it normalized? Seems to me that fldType has the same problems as
>described in the list box article. As a practical matter I would NOT
>create separate tables since different rollups are needed all the time
>and the programmers would poison my coffee if I proposed separate
>tables. I have already lost too many tasters that way. Along the same
>vein you COULD add a type field to the general ledger balance file to
>represent plan, actual or statistics but I would never combine those
>disimilar types of data. So the line beween correct normalization and
>practicality often blurs for me. I think there is some grey and
>judgement in creating truly normalized table schema. After all who can
>truly say what is Normal? :-)
>
>Jim Hale