My first Publication Agile-Data-Warehouse-Design-eBook | Page 163

142
Chapter 5
Database Key Definitions
Primary key (PK): A column or combination of columns that uniquely identifies
each row in a table. In addition to being unique, a primary key should ideally be:
Stable: not change value over time.
Minimal: be short, use as few columns as possible (ideally 1).
Not Null: be present, have a value for all rows in the table.
Foreign key (FK): A column or combination of columns in one (child or referenc-
ing) table that relate to the primary key of another (parent or referenced) table. In
a dimensional model, foreign keys within a fact table relate to the primary keys of
its matching dimensions.
Composite Key: A key made up of two or more columns. Identified in a BEAM✲
model by numbering a group of key codes alike; e.g., two columns in the same
table marked PK1 represent a two-part composite primary key.
Alternate Key: A column or combination of columns that can be used in place of
a primary key. Identified by numbering alternatives differently; e.g., three col-
umns in the same table marked PK1, PK2, PK2 represent a primary key and a
composite alternate key.
Candidate Key: A column or combination of columns that could act as a key.
Natural key (NK): A key that is used to uniquely identify something in the “real-
world” outside of a database; e.g., a barcode printed on a product package or a
Social Security number on an ID card. Natural key values are sometimes known
by stakeholders and used directly in reports and queries. The Employee ID 007
belonging to our favorite salesperson James Bond, has taken on a life of its own
beyond the HR system and become a natural key.
Surrogate key (SK): A key with a “meaningless” or artificial value, typically a
sequence number, generated by a database or application that is used instead
of a natural key.
Business key (BK): A primary key from a source system. This can be a mean-
ingful natural key or a meaningless system-generated surrogate within the
source system, but by the time it reaches the data warehouse it has meaning to
the business outside of the warehouse and so is referred to as a business key.
Benefits of Data Warehouse Surrogate Keys
Surrogate keys =
big DW/BI benefits
Data warehouse surrogate keys, referred to simply as surrogate keys, have the
follow benefits over source system business keys: