Keys, Relationship Patterns, and Hierarchies

Keys identify facts and enforce uniqueness; relationships reference those identities with domain-specific update and delete behavior. Surrogate keys provide stable internal identity, while natural candidate keys still need constraints. Hierarchies require explicit choices about traversal, cycles, moves, depth, and consistency rather than a fashionable tree representation.

What will you be able to do?

• Distinguish primary, candidate, natural, surrogate, and foreign keys.
• Model one-to-one, one-to-many, and many-to-many relationships.
• Choose delete/update actions deliberately.
• Compare adjacency lists, materialized paths, and closure tables.

Prerequisites and mental model

A key is a fact’s identity contract. “Unique today” is not enough; ask whether it changes, can be recycled, is known at creation, leaks information, or has tenant scope.

Which key should you choose?

SignalDesk uses generated bigint internal IDs and unique tenant-scoped business keys such as (organization_id, email). A UUID can improve distributed generation and opacity, but it does not provide authorization and has index/locality tradeoffs. Preserve all real candidate keys with unique constraints even when the primary key is surrogate.

Relationship patterns:

The join table is a real relation and can carry authorship or ordering.

How do you model hierarchies?

Knowledge categories can use an adjacency list: parent_id references the same table. It is simple for direct-parent writes and recursive reads, but cycle prevention and subtree operations need care. Materialized paths optimize ancestry/prefix operations but require path maintenance. Closure tables store all ancestor/descendant pairs and make reads fast at higher write/storage cost.

Choose from actual operations: maximum depth, move frequency, subtree queries, tenant scope, and consistency requirements.

Why does this matter in AI-native systems?

Stable document, chunk, conversation, and tool-call identities are required for citations, deduplication, idempotency, and evaluation. A model must not invent or infer authorization from an opaque ID. Hierarchies used for knowledge scopes need cycle and tenant controls before they become retrieval filters.

Prove it worked

Insert duplicate tag names within one tenant and across two tenants; only the same-tenant duplicate should fail. Try a cross-tenant ticket/tag assignment; it must fail. For a hierarchy, test root, leaf, move, maximum depth, and attempted cycle.

Production judgment

• Cascades can delete large graphs and create lock/WAL pressure.
• Changing primary keys propagates complexity; prefer immutable identities.
• Global sequences may reveal approximate volume but are rarely a security boundary.
• Recursive queries need bounds and appropriate indexes.

Common mistakes

• Duplicate business entity: surrogate key added but candidate key omitted → add domain uniqueness.
• Relationship metadata lost: IDs stored in an array → use a join relation.
• Tree recursion loops: cycles not prevented → enforce or detect cycles and bound traversal.
• Delete causes surprise: default action not designed → test each lifecycle path.

AI Pair-Programmer Prompt

Hands-on exercise

Model threaded message replies within a ticket using a self-reference that cannot point across tickets or tenants.

Acceptance criterion: valid replies work, cross-ticket and cross-tenant parents fail, and your traversal has deterministic order and a depth guard.

Knowledge check

1. Why keep a candidate-key constraint with a surrogate primary key?
2. Is a UUID an authorization mechanism?
3. What makes a join table more than plumbing?

---

Answers

1. The surrogate identifies rows but does not enforce domain uniqueness.
2. No; access must be checked independently.
3. It represents a relationship fact and can carry its own attributes and lifecycle.

Completion checklist

• Stable identities and business uniqueness are separate.
• Relationship tables preserve tenant scope.
• Delete actions match lifecycle policy.
• Hierarchies address cycles, moves, depth, and traversal.

Primary references

• Constraints
• Recursive queries