Moderately normalized schema: Balanced storage space and query performance

The following diagram shows an example of a moderately normalized schema. This schema type has the same basic structure as the highly normalized schema. The difference here is the higher-level attribute ID columns are present within all tables of related attributes. For example, Region_id is included in the Lookup_Call_Ctr table.

The fact table structure within a moderately normalized schema is identical to that of the highly normalized schema. The following diagram shows what the physical lookup tables look like in the warehouse.

Using a moderately normalized schema provides a balance between the pros and cons of normalized and denormalized schema types. Because the ID columns of both the parents and grandparents of an attribute exist in multiple tables, fewer joins are required when retrieving information about an attribute.

However, since some tables contain the same ID columns (as shown above with the Region_ID column), the tables within this type of schema take up some redundant storage space in the warehouse.