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
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
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.