The Recipe Card Itself: Keys, Candidate Keys, and Determining Structure

Opening Scene

Before a distiller can talk sensibly about which readings belong to which batch, one thing has to be settled: what uniquely identifies a batch in the first place? A batch number works. So might a combination of the still it was run on and the exact start time, if that combination never repeats. Whatever’s chosen, it has to genuinely, reliably distinguish one batch from every other — otherwise every later record-keeping decision built on top of it inherits the same fundamental ambiguity.

A key provides this exact same reliable, foundational identification in a database table.

In Plain English

A key is a column, or combination of columns, that uniquely identifies each row in a table. A candidate key is any column or combination that could serve this role; a table often has more than one genuine candidate. A primary key is the one actually chosen to serve this role in practice. A composite key is a key made of more than one column together, needed when no single column is unique on its own. Understanding keys precisely is the necessary foundation for everything the rest of this series covers, since every normal form is ultimately defined in terms of how non-key attributes relate to a table’s keys.

The Old Way

Correctly identifying keys has always been considered the essential first step of relational database design, preceding normalization itself:

A candidate key must be genuinely unique across every row, now and for the foreseeable future — a “unique enough for now” attribute that could plausibly repeat later is a real, latent design risk.
Choosing among multiple genuine candidate keys — a primary key selection — often comes down to practical factors like stability (an attribute unlikely to ever need to change) and simplicity (fewer columns is generally easier to reference elsewhere).
Composite keys are sometimes genuinely necessary, particularly in tables representing a relationship between two other entities, where no single column is unique but the combination of two foreign keys together reliably is.

Getting this right has always meant testing a proposed key rigorously against real or plausible future data, rather than assuming uniqueness based on a small, convenient sample that happens not to reveal a genuine duplicate.

What’s Changing (and Why AI Is the Reason)

AI-assisted key discovery can analyze real data and identify genuine candidate keys automatically, testing uniqueness against the actual dataset rather than relying on a modeler’s assumption. Rather than a modeler guessing which column or combination is unique, AI-assisted analysis can systematically test candidates against real data, including catching a plausible-looking but actually duplicated candidate that manual review might miss.
AI-assisted stability analysis can flag when a chosen primary key attribute has genuine risk of changing in the future, informing a more durable key selection upfront. An attribute that seems permanently fixed today — like an email address used as a key — may genuinely need to change later; AI-assisted analysis of how similar attributes have behaved elsewhere can flag this risk before it becomes a costly migration.
AI agents generating queries need to correctly understand a table’s actual key structure to join tables correctly and avoid producing accidentally duplicated or missing results. A query that assumes a single-column key where a composite key is actually required will silently produce wrong results; clear key metadata helps an agent generate genuinely correct joins.

The Metaphor, Fully Extended

Distillery Element	Key Concept
A batch number that reliably distinguishes one batch from every other	A key, uniquely identifying each row in a table
Several possible ways to identify a batch, all of which would genuinely work	Candidate keys, any column or combination capable of serving as the unique identifier
The one identification method the distillery actually adopts and uses consistently	The primary key, the candidate key actually chosen for practical use
Identifying a specific transfer by both the source and destination vessel together, since neither alone is unique	A composite key, made of more than one column together
A quality inspector testing a proposed batch numbering scheme against years of real production records	AI-assisted key discovery testing candidate keys against real data for genuine uniqueness

For Beginners: What to Actually Do

Practice identifying a table’s actual key before analyzing anything else about it — every other normalization concept in this series depends on understanding this first.
Get comfortable with the distinction between a candidate key (anything that could work) and a primary key (the one actually chosen).
Before trusting that a column is a valid key, test it against a real sample of data, or at least ask explicitly whether a duplicate is genuinely impossible, not just currently absent.
Notice that composite keys are common and legitimate, not a sign of poor design — many genuine relationships require more than one column to identify uniquely.

For Practitioners and Leaders: The Deeper Layer

Use AI-assisted key discovery to systematically verify candidate keys against real data, catching plausible-looking but actually duplicated candidates before they become a production problem.
Use AI-assisted stability analysis to inform more durable primary key selections, reducing costly future migrations caused by a key attribute that turned out to need to change.
Maintain clear, explicit key metadata across your schemas, since AI agents generating queries depend on correctly understanding key structure to avoid silently wrong joins.
Treat key identification as the genuine foundation of schema design, deserving the same rigor as any other structural decision covered throughout this series.

Quick Recap

A key uniquely identifies each row in a table; a candidate key is anything that could serve this role, a primary key is the one actually chosen, and a composite key combines multiple columns when no single one is unique.
Rigorously testing a proposed key’s uniqueness against real or plausible future data has always been the essential first step of relational design.
AI-assisted key discovery can systematically test candidates against real data, and AI-assisted stability analysis can inform more durable key selections upfront.
AI agents generating queries depend on correctly understanding a table’s actual key structure to avoid producing silently wrong, duplicated, or missing results.

Where This Fits in the Series

Article 1 established why normalization matters at all. This article covered the foundational concept everything else builds on — keys. Article 3 looks at what actually determines what — functional dependencies.