Highly normalized schema: Minimal storage space

The following diagram is an example of a highly normalized schema. In highly normalized schemas, lookup tables contain unique developer-designed attribute keys, such as Call_Ctr_id, Dist_Ctr_id, and Region_id, as shown in the figure below. They also contain attribute description columns, such as Call_Ctr_desc, Dist_Ctr_desc, and Region_desc. Also, the lookup table for an attribute contains the ID column of the parent attribute, such as Dist_Ctr_id in the Lookup_Call_Ctr table.

The following diagram shows what physical lookup tables look like in the warehouse:

One benefit of using a highly normalized schema is that it requires minimal storage space in the warehouse because of it uses smaller lookup tables than the other schema types.

However, there is a drawback to using only small tables in the data warehouse. When accessing higher-level lookup tables such as Lookup_Region in the example above, numerous joins are required to retrieve information about the higher-level tables. This is because each table contains only a small amount of information about a given attribute; therefore, multiple tables must be joined until the required column is found.