137 ) Surrogate keys

 137 ) Surrogate keys     

What is a Surrogate Key?

A surrogate key is an artificial, system-generated unique identifier (typically an auto-incrementing integer, sequential number, or a UUID) assigned to a table row. It has no business or natural meaning (e.g., an employee ID like 1005 rather than their Social Security Number or email).

When to Use: OLTP vs. OLAP

1. In OLTP (Online Transaction Processing) Systems

• When to use: Use surrogate keys as the primary key for almost all transactional tables.

• Why:

  • Immutability: Natural business keys (like email addresses, phone numbers, or usernames) change over time. If a natural key is used as a foreign key across multiple tables, updating it becomes a massive, error-prone cascading operation. A surrogate key never changes.

  • Simplicity and Performance: Single-integer surrogate keys provide fast, compact joins across relational tables compared to multi-column natural keys or long string-based natural keys.

2. In OLAP (Online Analytical Processing) / Data Warehouses

• When to use: Mandatory in dimensional modeling (Star Schema / Snowflake Schema) for Dimension Tables (e.g., Dim_Customer, Dim_Product).

• Why:

  • Handling Slowly Changing Dimensions (SCD Type 2): In data warehouses, if a customer moves or changes their last name, you often want to keep historical sales tied to their old profile while creating a new record for their new profile. A surrogate key allows you to have multiple rows for the same natural business entity (e.g., two different surrogate keys for the same customer ID across different timelines).

  • Performance & Integration: They insulate the data warehouse from changes in source system schemas (e.g., if the source system changes its ID format from numeric to alphanumeric).

Real-World Example & Database Scenario

Imagine an E-Commerce Database (can be implemented in PostgreSQL, MySQL, SQL Server, or Snowflake):

The Setup (Natural Key vs. Surrogate Key)

• Natural Key: customer_email (e.g., user@example.com)

• Surrogate Key: customer_key (an auto-incrementing integer like 101, 102)

The Use Case / Failure of a Natural Key:

1. Customer John Doe registers with the email john.old@gmail.com.

2. He places 50 orders over two years. His email is stored as the foreign key in the orders table.

3. John changes his email to john.new@gmail.com.

4. If using a Natural Key: You must update the orders table across millions of historical rows (or break historical tracking if updates aren't cascaded).

5. If using a Surrogate Key: John's underlying customer_key remains 5042. His profile row updates its email string, but zero historical order records need to change because they all point safely to customer_key = 5042.

Example Implementation (PostgreSQL):

SQL

-- Creating a table using a surrogate key (id)
CREATE TABLE customers (
    customer_key SERIAL PRIMARY KEY,       -- Surrogate Key (System generated)
    customer_code VARCHAR(50) UNIQUE,      -- Natural/Business Key (e.g., source system ID)
    email VARCHAR(255),
    full_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_key INT,                      -- Foreign key pointing to the surrogate key
    order_date TIMESTAMP,
    total_amount DECIMAL(10,2),
    CONSTRAINT fk_customer FOREIGN KEY (customer_key) REFERENCES customers(customer_key)
);