Business key

What is a business key?

A business key is the identifier a business already uses to recognise something in the real world: an invoice number, a VAT or company registration number, an EAN barcode, an employee number. People read them off documents and quote them over the phone. The key lives in the business, not only in the database.

The same idea is often called a natural key, because it falls out of how the business runs rather than being invented by a system. That is what makes it useful, and what makes it awkward as a technical identifier.

Why you integrate and match on business keys

A business key is what lets you recognise the same thing across systems that were never designed to talk to each other. Say your CRM and your ERP both hold a Belgian company number for an account. Each also has its own internal record ID, and those two IDs mean nothing to one another. The company number is the value they share, so it is the value you join and match on.

That is why business keys sit at the centre of integration work. In master data management and entity resolution, a matching business key is one of the strongest signals that two records describe the same customer or product. In a data warehouse built with Data Vault, the hub tables are lists of business keys, because a business key is the stable point every source system agrees on. It also helps data lineage: a record carrying a real invoice number traces straight back to the source document.

Why a business key makes an unreliable primary key

Being recognisable is not the same as being dependable. A business key that reads perfectly to a human often breaks the moment you make it the physical primary key of a table.

• They change. A company number can be reassigned after a merger, a product re-coded, a customer number shifted during a migration. A primary key is meant to be permanent.

• They get reused. Some systems retire a customer or article number and later hand the same value to a different entity, quietly corrupting the history attached to it.

• They differ per system. One customer may carry three different numbers in three applications, and none is guaranteed unique once you pull everything together.

• They go missing. A foreign customer with no local registration number, or a record from before a numbering scheme existed, leaves you with no key at all.

There is also the slowly changing dimensions problem: once you keep history, one real-world thing produces several rows over time, so one business key no longer points at one row.

Business key versus surrogate key

This is why a data warehouse usually keeps both. A surrogate key is a meaningless integer the warehouse generates and controls. Because the warehouse owns it, it never changes or clashes, which makes it a safe primary key and a safe foreign key between fact and dimension tables.

The business key stays in the table as an attribute. You still store it and match on it, but you no longer ask it to guarantee uniqueness or permanence. In relational terms it is one of the candidate keys the business recognises, sometimes a composite key across several columns, while the surrogate is the technical primary key. Keep the business key for meaning and matching, and let the surrogate carry the joins.