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
Region_id, as shown in the figure below. They also contain attribute description columns, such as
Region_desc. Also, the lookup table for an attribute contains the ID column of the parent attribute, such as
Dist_Ctr_id in the
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.