Can individually normalized tables still repeat information across the schema?

Yes. Inter-record redundancy highlights that wider view.

Is every copied value bad design?

No. Copies can support performance or history, but their purpose and consistency rules need to be explicit.

Is a historical snapshot the same as a current cache?

No. A snapshot records a value at a defined time, while a current cache aims to reflect an authoritative present value.

AI Glossary / Data engineering and architecture / Intermediate

Inter-record redundancy

Definition

Inter-record redundancy, in William Kent's database-design discussion, refers to redundant information across multiple record types or relations. A fact may be stored directly in one table even though it can already be derived from other tables. Each table can appear well normalized on its own while the overall design repeats information. This specific historical usage is broader than duplicate rows within one table.

What it means

Suppose each training session has one course, and each course has one designated level. A separate table storing each session's course level may repeat a fact already obtainable through the session-course and course-level relationships. If the copies disagree, the system needs a rule for which is authoritative. The redundancy is not visible merely by examining the keys and dependencies inside each individual table.

How it works

Review relationships across the schema and identify values that are derivable from other maintained facts under the business rules. Decide whether a derived value should be calculated, exposed through a view, or stored deliberately with a maintenance process. Some duplication is useful for performance, history, or availability, but its meaning and refresh behavior must be explicit. A historical snapshot may be a different fact from the current derived value, so identical-looking fields are not automatically redundant.

Three practical examples

Reporting models: a summary table stores a value already derivable from detailed records and therefore needs a documented refresh policy.
Application schemas: an entity's inherited classification is stored both through a relationship and directly on the entity, creating a consistency requirement.
AI datasets: derived source metadata is copied into training records, requiring a clear snapshot date and lineage so later source changes are not mistaken for corruption.

Think of it this way

Think of keeping the same appointment in two calendars without a synchronization rule. Either copy may become stale when the appointment changes. The analogy is limited because some database copies intentionally capture different times or serve different workloads. A calendar entry recording what was originally promised is not necessarily redundant with the current schedule; the semantics determine whether the facts are the same.

Worked example

A toy SessionCourse table records S1 → C1 and S2 → C1. CourseLevel records C1 → Introductory. Together they imply that both sessions currently use an introductory course. A separate SessionLevel table repeats S1 → Introductory and S2 → Introductory. If C1's classification changes to Intermediate, updating only CourseLevel leaves conflicting representations. The design can calculate session level through the two relationships instead, or maintain SessionLevel as an explicitly derived cache. If SessionLevel instead means “level advertised when booking opened,” it is a historical fact and should be named and dated accordingly. It should not be overwritten merely to match the current course classification. The example shows that derivability and time semantics are both essential to diagnosing redundancy.

Illustrative case study

Fictional example: Pine Workshops discovers different level labels in its catalog and recommendation dataset. Investigation shows that one copy was intended as a current lookup and the other as an enrollment-time snapshot, but neither was documented. The team clarifies the two meanings and renames the fields. Where a current cache truly duplicates the catalog, it adds a controlled refresh process. This invented case illustrates semantic review, not a claim that every disagreement between two stored values is an error.

Common misconceptions and mistakes

Do not assume that normalizing each table independently removes all redundancy across the database. Do not equate repeated identifiers used as foreign keys with unnecessary duplication; they may be essential relationships. Avoid deleting copies before understanding their historical or operational purpose. Also distinguish intentional replication and materialization from uncontrolled duplicate facts. The important questions are whether the copies represent the same fact, which source governs it, and how consistency or snapshot meaning is maintained.

Business use

The concept supports architecture reviews, data governance, and trustworthy analytical exports. Trace derived attributes to their inputs and record the dependency. Choose whether to compute or store based on measured needs, then define refresh timing and failure handling for stored derivatives. For AI datasets, retain the source snapshot and transformation version when copied values must remain reproducible. Do not silently update historical training records merely because current operational data changes unless that is the dataset's explicit maintenance policy.

Check your understanding

A table stores a customer's current region, and another stores the region at the time of an order. Are these necessarily redundant copies?

Answer: No. They represent different temporal facts. If both fields claim to represent the current region, however, the design needs a consistent derivation or maintenance rule.

Related terms

Data redundancy
Normalization
Materialized view
inter-relational dependency
denormalization
derived attribute
cache invalidation
historical snapshot
data lineage
source of truth

Learn next

choose one derived field stored in a report table and trace the inputs that determine it. Write whether it represents current state or a historical snapshot. Simulate an input change and explain which stored values should change, which should remain fixed, and how that behavior is enforced.

Sources

Sources: William Kent, A Simple Guide to Five Normal Forms in Relational Database Theory, section 6, https://www.bkent.net/Doc/simple5.htm . The original author's article and inter-record redundancy section were read. Its central point grounds the historical term; the session-level example, temporal distinction, and operational guidance are original elaborations. The entry does not claim that ordinary per-relation normalization alone resolves every cross-relation dependency.

Previous: Inter-rater agreement Next: Inter-relational constraint