A report shows three spellings for the same status. Reference data gives those values one governed home and a clear owner for changes.
Separate Codes From Display Names
A code is a stable key used by applications and joins. A display name is what people read. Those jobs differ. A status called In Progress can later be renamed without changing the code used in stored rows. Store both, and avoid using the visible label as the primary key.
I ask whether a code can be retired, reused, or translated. Reuse is dangerous when old facts remain. A retired code can stay in the lookup for historical joins while an active flag prevents new transactions. If translations are needed, keep them in a separate table keyed by code and locale.
The smallest useful reference table has a code, name, active state, and a sort order. Add effective dates only when the business meaning changes over time. The lookup should be boring. It is allowed to be boring.
CREATE TABLE dbo.OrderStatus
(
StatusCode varchar(20) NOT NULL PRIMARY KEY,
StatusName nvarchar(100) NOT NULL,
IsActive bit NOT NULL DEFAULT 1,
SortOrder int NOT NULL
);Give Reference Data an Owner
A lookup table needs a person or team that decides which codes exist and what each means. The DBA can enforce structure, but should not decide whether Pending Review and Awaiting Approval are equivalent business states. Record the owner and approval path.
I have seen reference values added through a quick production INSERT because a new source file arrived. The load succeeds, but test and reporting environments drift. Treat a code change like a small release: review the meaning, deploy it, and verify consumers.
Who can add a new status in your system? If the answer is any application account, the reference list is not governed. Restrict writes, and let consumers read through a stable table or view. A simple ownership rule prevents a surprising amount of cleanup.
Choose One Authoritative Home for Reference Data
When several systems use the same codes, name the authoritative source and the distribution path. One application database can own the list, with a controlled copy or feed to reporting databases. Avoid two teams editing separate copies and calling both master data.
Not every lookup must be centralized. A source-specific error code can remain near its source. A shared customer category that drives finance and operations needs stronger coordination. The decision depends on business reach, not table size.
I prefer a documented one-way flow. Changes move from the owner to downstream copies. Reconciliation queries compare code sets and meanings. A two-way sync between lookup tables creates conflict rules for a problem that should have had one owner.
SELECT StatusCode, StatusName, IsActive
FROM dbo.OrderStatus
ORDER BY SortOrder, StatusCode;
Protect Historical Meaning
Do not delete a code just because users can no longer select it. Old rows still reference it. Keep the code and mark it inactive. If the meaning changed, decide whether to create a new code rather than rewriting the old label. Rewriting history can make an old report appear to say something it never said.
Effective dates are useful when the label or classification must reflect the date of a transaction. The lookup then needs nonoverlapping validity ranges and joins that use the fact date. That is more work, so use it only when historical meaning matters.
I check how reports handle an unknown code. An INNER JOIN can silently drop facts if a new code has not reached the reporting copy. A LEFT JOIN that exposes Unknown Code makes the drift visible. The report should not reward a missing lookup with a smaller total.
Keep Reference Data in Step Across Environments
Reference data should travel through the same controlled deployment path as schema and procedures. Seed scripts or deployment packages can insert new codes and update approved names. Make scripts idempotent so a rerun does not duplicate values or overwrite local state unexpectedly.
Compare development, test, and production codes before a release. Differences can be intentional, but they should be explained. A test environment missing a new status cannot prove that the production load will handle it.
I keep a small reconciliation query available. It compares code and name pairs between an authoritative table and a staged copy. Review mismatches before promoting a pipeline. A green build says little if the lookup list differs.
SELECT a.StatusCode, a.StatusName AS SourceName,
b.StatusName AS TargetName
FROM dbo.OrderStatus AS a
FULL JOIN dbo.OrderStatusStage AS b
ON b.StatusCode = a.StatusCode
WHERE a.StatusCode IS NULL
OR b.StatusCode IS NULL
OR a.StatusName <> b.StatusName;Use Constraints to Keep Codes Honest
A foreign key from transaction rows to the lookup protects against unknown codes. It also makes load order important. Publish approved lookup changes before loading facts that use them. When external data contains unknown codes, reject or quarantine those rows rather than disabling the constraint and hoping for a later cleanup.
A unique code constraint is essential. Consider a separate uniqueness rule on active names only if the business requires it. Two codes can legitimately share a display name in some domains, so do not assume that names are unique identifiers.
If a lookup has a parent hierarchy, validate cycles and orphaned parents. A status list rarely needs a hierarchy, while geographic or product classifications sometimes do. Add the structure that the domain requires, and write a query that can check it.
Review Changes Like Small Data Releases
A reference data change can alter reports, validations, and user choices. Keep a change log with who approved it, when it becomes active, and which consumers were checked. The log need not be elaborate. It should make a changed code explainable six months later.
Test a new code through one complete path: source entry, ETL, target foreign key, report label, and any filter list. A code that exists only in the lookup table is not fully released. I want to see it in the place users actually encounter it.
Stable codes, readable labels, ownership, and synchronized copies make reference data dependable. None of that requires a large platform. It requires treating a small table as shared business language instead of as a convenient place to store strings.
A lookup value can be shared while its local label differs by audience. If that requirement appears, keep the stable code authoritative and manage presentation labels separately. Do not create a second code just to change wording on one report.
Related reading on this blog: Adding Reference Data to Master Data Services: Notes from the Field #081 and Find Untrusted Foreign Key.
Reference data is not a bag of labels, it is a governed vocabulary for the system.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
