Types of Keys Relevant to Dimensional Modeling
https://www.leapfrogbi.com/ What is a surrogate key? What is a foreign key? What is a business or natural key? It is important to know the meaning of these key types and others to effectively design and read dimensional models.
Types of Keys Relevant to Dimensional Modeling
Dimensional Modeling Keys
In this section, the video introduces and discusses various types of keys related to dimensional modeling, categorizing them into five main groups.
Business and Natural Keys
- A business key or natural key uniquely identifies an entity within a system.
- Business keys like customer IDs represent individual entities such as Jane Doe or Jack Brown.
- These keys are utilized in dimensions within the model.
Primary, Candidate, and Alternate Keys
- Primary keys uniquely identify records in a table.
- Candidate keys like customer ID, name, and state contain unique records; one is selected as the primary key while others become alternate keys.
Composite, Compound, or Concatenated Keys
- These keys use multiple fields to form a unique identifier.
- Combining fields like name and state into a composite key creates a candidate key when individual fields have duplicates.
Surrogate Keys and Foreign Keys
This part delves into surrogate keys that lack business meaning but serve as primary identifiers in dimensions. It also explores foreign keys linking tables within the model.
Surrogate Keys
- Surrogate keys have no inherent business significance and are typically auto-generated.
- In dimensions, these surrogate keys uniquely identify records or versions of entities without carrying any business context.
Foreign Keys
- Foreign keys establish relationships between tables by pointing to primary keys in other tables.