Warehouse Structure for Your Logical Data Model » Tables: Physical groupings of related data » Uniquely identifying data in tables with key structures

Uniquely identifying data in tables with key structures

In relational databases, each table has a primary key that creates a unique value identifying each distinct data record or row. This applies to every type of table within the data warehouse.

The types of keys that can be assigned to a table include:

Simple key requires only one column to identify a record uniquely within a table.
Compound key requires multiple columns to identify a unique record.

Which key structure you use to identify a unique attribute in a table depends on the nature of your data and business requirements. The following diagram shows how the different key structures can be used to identify a calling center.

The simple key shows how you can identify a calling center with only its Call_Ctr_id. This means that every calling center has its own unique ID.

In the compound key, calling centers are identified by both Call_Ctr_id and Region_id. This means that two calling centers from different regions can share the same Call_Ctr_id. For example, there can be a calling center with ID 1 in region A, and another calling center with ID 1 in region B. In this case, you cannot identify a unique calling center without knowing both the Call_Ctr_id and the Region_id.

Simple keys are generally easier to handle in the data warehouse than are compound keys because they require less storage space and they allow for simpler SQL. Compound keys tend to increase SQL query complexity, query time, and required storage space. However, compound keys have a more efficient ETL process.

Which key structure you use for a particular attribute depends entirely on the nature of the data and your system. Consider what key structures work best when creating lookup tables in the physical warehouse schema.

For information on defining the primary key for tables included in a MicroStrategy project, see Defining the primary key for a table.