Why Historical Approval Records Change: The Missing Snapshot Fields
The article reveals how storing only foreign keys in approval records causes historical data to reflect current organizational changes, and demonstrates adding snapshot fields to preserve the state at approval time, distinguishing between current relationships and historical facts.
The author recounts a real-world issue in an approval system: a three-year-old purchase approval record suddenly displayed the employee's current department and an updated approver name after an organizational restructuring, even though the database records had not been modified.
The root cause was a seemingly clean table design that stored only foreign keys:
create table approval_instance (
id bigint primary key,
applicant_id bigint not null,
approver_id bigint not null,
department_id bigint not null,
status varchar(32) not null,
created_at datetime not null
);At display time, the application joined to the current sys_user and sys_department tables:
select a.id,
u.real_name as applicant_name,
d.name as department_name,
au.real_name as approver_name
from approval_instance a
left join sys_user u on u.id = a.applicant_id
left join sys_department d on d.id = a.department_id
left join sys_user au on au.id = a.approver_id
where a.id = ?;While this design avoids redundancy and keeps the schema normalized, it conflates two fundamentally different types of data: current state (what the user/department looks like now) and historical fact (what they looked like when the approval happened). The article argues that an approval record is a historical business document — it should not change when the organization changes.
The Core Problem: Foreign Keys Only Capture Identity, Not State
When the employee moved from "East China Sales Division 1" to "Channel Center" and the approver's display name changed from "Zhang Wei" to "Zhang Wei (Shanghai)", the historical approval page began showing the new values. Technically the SQL was correct — it returned the latest data — but business-wise it rewrote history.
The author illustrates the timeline:
At approval time (2025-08): Applicant: Wang Qiang, Department: East China Sales Division 1, Approver: Zhang Wei.
Six months later: Org restructuring moves Wang Qiang to Channel Center; Zhang Wei's display name changes due to a naming conflict.
Result: The old approval now shows Department: Channel Center, Approver: Zhang Wei (Shanghai).
This is not a SQL bug; it is a data modeling error. The approval record saved who was involved (via IDs) but not what their state was at that moment.
Solution: Add Snapshot Fields
The team kept the foreign keys (needed for queries, permissions, statistics) but added a set of snapshot columns:
alter table approval_instance
add applicant_name varchar(100),
add department_name varchar(200),
add approver_name varchar(100);At creation time, the application copies the current values:
ApprovalInstance approval = new ApprovalInstance();
approval.setApplicantId(currentUser.getId());
approval.setApplicantName(currentUser.getRealName());
approval.setDepartmentId(currentDepartment.getId());
approval.setDepartmentName(currentDepartment.getName());
approval.setApproverId(approver.getId());
approval.setApproverName(approver.getRealName());These fields are not mere redundancy; they are snapshots — a deliberate capture of the business facts at the moment of the event.
Why Both ID and Name Are Needed
One might ask: if we have the snapshot name, can we drop the ID? No. Names are not unique identifiers (e.g., ten employees named "Zhang Wei"). The ID remains essential for linking to the current user entity for queries like "which approvals did Zhang Wei process?". The snapshot name answers "what should the page show for this historical record?". The two serve different purposes and coexist without conflict.
The Danger of Half-Snapshots
The article warns against partial snapshots. A contract table might store customer_id and customer_name but still fetch address, contact person, and phone from the live customer table. When the customer moves, a three-year-old contract prints with the new address — a more subtle but equally serious corruption of history. The rule: if a business document (contract, order, invoice, approval, ticket) includes certain fields at signing time, all those fields belong to the historical fact and should be snapshotted.
Orders Already Do This Naturally
E-commerce order details routinely store product_id, product_name, sku_name, sale_price, quantity. They do not re-read the product catalog at display time because the product name and price change. The article notes that OA, SaaS, CRM, and approval systems often forget this principle.
User Deletion Makes It Worse
When a user is logically deleted ( deleted = 1) or physically removed, a foreign-key-only record returns Optional.empty(), leaving the approval page showing "Approver: --". A snapshot approver_name = 'Zhang Wei' preserves readability. Historical data must stand on its own; it cannot rely on the survival of master data.
Don't Snapshot the Entire User Object
Some teams serialize the whole user object as JSON. The author advises against this: a snapshot should contain only the fields that are meaningful for the business event (e.g., applicant name, department, position). Copying phone, last login, avatar, password change time, etc., bloats the record and obscures intent. A snapshot is a business fact snapshot , not a backup of the user table.
Model Snapshots as Value Objects
To avoid scattering dozens of applicant_xxx, approver_xxx columns across the entity, the author recommends a dedicated value object (Java record or JPA @Embeddable):
public record UserSnapshot(
Long userId,
String userName,
Long departmentId,
String departmentName,
String positionName
) {}With JPA:
@Embeddable
public class ApplicantSnapshot {
private Long userId;
private String userName;
private Long departmentId;
private String departmentName;
private String positionName;
}
@Embedded
@AttributeOverrides({
@AttributeOverride(name = "userId", column = @Column(name = "applicant_id")),
@AttributeOverride(name = "userName", column = @Column(name = "applicant_name"))
})
private ApplicantSnapshot applicant;This makes the semantic intent explicit in code: "this is the applicant snapshot at approval time."
A Practical Decision Rule: Current State vs. Historical Fact
The team adopted a simple test for each displayed field:
If the field answers "What does this object look like now ?" → read from master data (current phone, current department, current assignee, current inventory).
If the field answers "What did it look like when the event happened ?" → use a snapshot (product name at order time, customer name at signing, department at approval, fee rate at payment, customer tier at ticket creation).
Reframing "should we denormalize?" into "current state or historical fact?" resolves many debates.
Some Fields Need Versioning, Not Just Snapshots
For rules, templates, and policies (pricing rules, approval workflows, risk models), a snapshot of the output value (e.g., final price 1268.50) is insufficient. Six months later, when asked "why this price?", the system must know which rule version produced it. The author stores rule_version, policy_version, template_version alongside the result:
PricingResult result = pricingService.calculate(request);
order.setPrice(result.price());
order.setPricingRuleVersion(result.ruleVersion());This extends the same principle: don't use today's rules to explain yesterday's outcomes.
Audit and Regulatory Requirements May Demand More
For high-audit scenarios, a name alone may not suffice (users can rename, accounts can be reassigned). The historical record may need: user_id, employee_no, user_name, department_id, department_name, plus identity source, role at the time, and operation timestamp. The principle remains: save the minimum information necessary to prove "who did what in what capacity at that time."
These Bugs Surface Years Later
Unlike performance issues that appear under load, snapshot omissions lie dormant. At launch, no one has transferred, renamed, or left. The problem emerges after 1-2 years when org changes accumulate. By then, the original state is often irrecoverable — a far costlier problem than a slow query.
Retrofitting an Existing System
For new records, start snapshotting immediately. For three years of existing approvals, if the system lacks user/department change logs, you can only backfill with current names (which may not match the historical ones). If audit logs or history tables exist, you can join on approval.created_at to reconstruct the past state. This underscores the value of maintaining history tables even when they seem wasteful.
Never Re-Sync Snapshots to Master Data
A common mistake: seeing approval.applicant_name differ from sys_user.real_name and writing a sync job to update all historical records. That defeats the purpose. A snapshot, by definition, does not change after creation . The two values are allowed to differ — one represents "now", the other "then".
Query Layer Must Respect Snapshots
Even with snapshot columns in the database, developers may still write queries that join to the live user table and overwrite the snapshot in the DTO. The historical view must explicitly use approval.getApplicantName(); the current user view uses user.getRealName(). The code must keep the two semantics separate.
The Pattern Applies Everywhere
After fixing approvals, the team found the same risk in contracts (only customer_id), tickets (only handler_user_id), bills (only tenant_id), notifications (only template_id), and reports (live org joins). The fix is not universal — each case depends on whether the page shows current state or historical fact.
A New Design Habit
The author now asks an extra question when designing tables: "Five years from now, when I open this record, do I want to see today's value or the value from that day?" If the answer is "the value from that day", a foreign key alone is insufficient. The key insight: some data must be duplicated precisely because history must not change along with the present.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
