Lesson 4	Entity Identifier Rules
Objective	List two rules for creating entity identifiers.

Two Rules for Creating Entity Identifiers

The two rules are to keep identifier values meaningless and unrestricted: avoid encoding changeable business facts in a key, and avoid unnecessary limits on the people, objects, or future records that the key can identify. These guidelines help keep references stable as a system grows.

Neither rule removes the need for constraints. An identifier still needs a defined representation, a uniqueness rule, and an allocation process. Nor do the rules forbid every natural or composite key. They are practical guidance for selecting flexible identifiers, especially surrogate keys, rather than a complete definition of relational correctness.

The previous lesson explained the purpose of identifiers. This lesson asks how to choose values that continue to serve that purpose when customers move, departments reorganize, systems merge, and new kinds of records arrive.

The Two Rules at a Glance

Keep identifier values meaningless. An internal key should ordinarily distinguish an instance without also encoding its region, department, status, name, or other mutable facts.
Keep identifier values unrestricted. Avoid requirements that unnecessarily exclude valid instances or limit growth, such as requiring every customer to have one country's government identifier.

Here, “meaningless” means independent of business interpretation, not random or unreadable. “Unrestricted” means free of unnecessary business restrictions, not infinite or unvalidated. A generated integer can follow both guidelines while remaining bounded by its data type and controlled by database constraints.

Applying the rules to a customer identifier
Design	Problem to investigate	Possible improvement
NY-GOLD-042	Region and membership level may change	Use an independent CustomerID; store region and level separately
A national personal identifier	Some customers may not have one; it may be sensitive	Assign an internal identifier available to every eligible customer
Three decimal digits	The available number range may be too small	Choose capacity based on expected lifetime allocation
Generated CustomerID	Assignment scope and duplicate handling still need rules	Define allocation, uniqueness, and lifecycle policies explicitly

Rule 1: Avoid Encoding Mutable Business Meaning

Suppose an employee identifier contains an office code: BOS-1042. If the employee transfers to another office, should the identifier change? Changing it can affect references in payroll, time tracking, exports, and other systems. Leaving it unchanged makes the embedded office code misleading.

A separate EmployeeID avoids that conflict. Store the employee's office as an attribute or a relationship to an Office entity. The employee keeps the same identity when the office assignment changes. If assignment history matters, record that history explicitly instead of expecting the identifier to explain it.

The same issue appears with customer status, product categories, organizational units, and dates embedded in keys. These facts may be useful to display or search, but that does not make them suitable parts of a permanent identifier. Ask whether the identifier must be rewritten whenever an ordinary business fact changes.

Meaningful reference numbers can still have a legitimate business purpose. A customer-facing order number may follow a required format, for example. You can maintain that number separately from an internal key, with its own uniqueness and lifecycle rules. Separating the roles lets the internal references remain stable if the display format changes.

Do not parse an internal key to recover facts that deserve their own attributes. A reporting query should obtain a customer's current region from the modeled region information, not infer it from characters in CustomerID. Otherwise two different representations of the same fact can disagree.

Rule 2: Avoid Unnecessary Population and Capacity Limits

An identifier must be available for every valid instance in the modeled population. Requiring a U.S. Social Security number for every customer excludes people without one and does not accommodate organizational customers. The problem begins with the population definition, before any SQL data type is chosen.

Phone numbers and email addresses raise other questions. People may share them, change them, or have several. An organization may permit multiple accounts to use one contact address. Their suitability as identifiers depends on explicit rules, not on their apparent uniqueness in a small sample.

Capacity also matters. A design allowing only three decimal digits has a limited allocation space. Plan for the lifetime number of records, including retired records and reserved values, rather than only the number active today. A larger data type postpones exhaustion, but every practical representation has limits.

Define the scope of uniqueness as well. Is the value unique in one table, one tenant, one installation, or across independently operating systems? Two installations can each generate customer 1001 without violating either local primary key. Combining their data requires a policy that distinguishes those identities.

Possible approaches include a centrally coordinated allocator, a key qualified by its source, or an identifier scheme designed for independent generation. Choose according to the actual architecture. Calling a value “global” in documentation does not make separate generators coordinate with one another.

Unrestricted identifiers still have validation rules. Document the data type, allowed format, case handling for text, maximum length, and who assigns values. If leading zeros are part of a code, preserve them intentionally. The goal is to remove accidental restrictions while retaining the rules that make identification reliable.

Meaningful Column Names, Independent Key Values

The first rule concerns stored values, not schema names. Names such as CustomerID, OrderID, and DepartmentID should clearly express what the column identifies. Naming every column Code or Number makes relationships harder to understand.

A value such as 1042 need not tell a user anything about the customer's location or status. That is compatible with a meaningful column name. Conversely, a column called CustomerID can contain a poorly designed value if the application constructs it from mutable business attributes.

Use consistent names for corresponding references, and document any exceptions. A readable schema helps maintainers follow relationships without assigning additional business meaning to the values themselves.

Uniqueness, Minimality, and Stability Are Different Properties

A candidate key uniquely identifies each instance and is minimal: removing any component would lose that identifying property. If CustomerID alone is sufficient, adding LastName creates an unnecessary component. Minimality concerns the set of attributes, not the number of printed characters.

Stability is a separate design preference. An identifier can be unique at every point in time yet change frequently. A unique email address might satisfy a particular account rule today, but email changes can still make it inconvenient as the key referenced throughout a system.

A primary key enforces uniqueness and non-null values. It does not by itself make the values immutable. If your design requires identifiers never to change after assignment, enforce that policy through the supported write paths, privileges, and appropriate database mechanisms. Do not describe a primary-key declaration as an immutability constraint.

Likewise, a generated value is not the same as an enforced uniqueness rule. Keep generation and integrity conceptually separate: one supplies a proposed value, while the other rejects a conflicting value. This distinction matters during imports, sequence resets, and system integration.

Finally, a unique record identifier does not prove that each real-world person appears only once. Two accounts with different CustomerID values may still belong to the same person. Duplicate-account detection and merging require business rules beyond the primary key.

Natural and Surrogate Keys: Make a Deliberate Choice

A natural key uses attributes already meaningful in the business domain. A surrogate key is introduced primarily to identify a record. A governed reference code can be a reasonable natural key when it is required, unique within the intended scope, and sufficiently stable.

Surrogate keys are useful when natural candidates change, expose sensitive information, contain many components, or are unavailable at record creation. They let references depend on a small stable identifier while the business attributes remain independently maintainable.

However, adding a surrogate key must not erase a real uniqueness requirement. If the organization guarantees that each customer has one unique account code, the database should enforce that rule even when CustomerID is the primary key. Otherwise two records could receive different internal IDs but incorrectly share the account code.

Do not invent uniqueness requirements merely to demonstrate SQL. Email should be unique only if the actual application requires that behavior and defines how addresses are compared. Shared contacts, historical values, and normalization rules can make the decision more complicated than a single column declaration suggests.

The two lesson rules therefore favor independent keys when they reduce avoidable coupling, while leaving room for well-governed natural keys. The decision should follow documented requirements rather than a blanket rule that every meaningful value is invalid.

SQL Example: Internal Key and Governed Business Code

This illustrative PostgreSQL 18 example assumes that every customer must have a required, unique AccountCode assigned by the organization. AccountCode is separate from the generated internal CustomerID. DisplayName is descriptive and is allowed to repeat.

CREATE TABLE Customer (
    CustomerID  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    AccountCode VARCHAR(30) NOT NULL,
    DisplayName VARCHAR(200) NOT NULL,
    CONSTRAINT uq_customer_account_code UNIQUE (AccountCode)
);

INSERT INTO Customer (AccountCode, DisplayName)
VALUES ('AC-1001', 'Alex Morgan')
RETURNING CustomerID, AccountCode, DisplayName;

The insert requests a generated CustomerID and returns the assigned value. The primary key checks the internal identifier; the separate uniqueness constraint checks AccountCode. Another customer may have the same DisplayName but must receive a different internal identifier and account code.

A later change to the account-code format need not change CustomerID. Related tables can continue referencing the internal key. The new account code still has to satisfy the account-code rules. The example does not assume that changing an identifier is automatically safe for external systems that use it.

PostgreSQL's identity-column documentation distinguishes automatic generation from uniqueness enforcement. The constraint documentation explains primary and unique keys. These statements are teaching examples, not results captured from an executed database session.

Plan Identifier Allocation and Lifecycle

Choose one authoritative allocation process for each identifier scope. Computing the next ID as the largest existing value plus one is unsafe when concurrent operations can choose the same result. Use an allocation mechanism appropriate to the DBMS or distributed architecture, together with enforced uniqueness.

Do not require an internal identifier sequence to be gapless. Failed or cancelled operations and allocation behavior can leave unused values. If a business document requires a controlled numbering sequence, treat that as a separate requirement with its own process rather than assuming the primary key supplies it.

Avoid assigning a retired identifier to a different entity while old references can survive. Archived records, audit trails, messages, and external integrations may still contain it. Reuse can make an old reference appear to identify a new person or object. Define retention and identity policies together.

Merging duplicate records also needs a controlled procedure. Select the surviving identity, reconcile dependent references, and preserve any mapping needed to interpret historical identifiers. Deleting one row without considering its relationships is not a complete merge strategy.

Opaque Identifiers Are Not Access Controls

A sequential number may carry no business meaning yet remain easy to predict. A UUID may be less convenient to guess, depending on how it is generated, but possession of an identifier must not be treated as permission to access the record.

The application must check whether the requesting user may read or modify the identified instance. Selecting an opaque key can reduce accidental exposure of business facts; it cannot replace authorization. Likewise, embedding sensitive personal information in a key can spread it into logs and references where it was never needed.

Associative Entities and Composite Identifiers

An associative entity represents an association that needs to be stored, such as a student's enrollment in a course offering. It still needs an identifier. “Associative” describes the entity's role, while “composite” describes a key formed from multiple attributes.

If one enrollment per student per offering is allowed, StudentID and OfferingID together may identify an enrollment. A surrogate EnrollmentID is another option, with a uniqueness constraint on that pair to preserve the rule. The surrogate option does not automatically make the design more correct.

First define what the row represents. StudentID plus CourseID may be too restrictive if students can take the same course in different terms. If repeated attempts within the same offering are valid, the student-offering pair also needs reconsideration. The identifier must distinguish the intended occurrence, not merely the broad concepts involved.

A composite key can be stable when its components are stable references. The advice to avoid mutable encoded meaning does not prohibit composite identifiers. Evaluate their size, reference requirements, and business semantics without confusing a structured key with a formatted string containing department or region information.

Common Pitfalls and Review Questions

Encoding changeable facts: What happens when the department, region, or status changes?
Excluding eligible instances: Can every legitimate customer or object receive an identifier?
Assuming unlimited capacity: Is the allocation space adequate for the expected lifetime of the system?
Assuming generation ensures integrity: Are uniqueness and mandatory values actually enforced?
Dropping business rules: Which alternate identifiers still need constraints after adding a surrogate key?
Confusing identity with permission: Does every operation check access to the requested record?

Test the design with changes as well as inserts: a customer moves, a code format changes, a second installation joins the system, or duplicate accounts are merged. A key that works only for today's sample data has not yet been evaluated against the system's lifecycle.

Apply the Two Rules

Keep identifier values independent of mutable business facts and free from unnecessary population or format restrictions. At the same time, define their representation, allocation scope, capacity, uniqueness, and lifecycle. Use readable column names and explicit constraints to make the design understandable and enforceable.

For a quick self-check: CustomerID is a meaningful column name and does not violate the first rule. An internal numeric ID is not unlimited merely because it contains no region code. A primary key does not automatically prevent updates to its values. A surrogate key does not replace valid business uniqueness rules.

Next, explore entity instances to connect these identifier decisions with the particular occurrences represented in a database. For the broader introductory context, revisit entities and attributes in database design.