Update anomaly

As explained in Grokking Relational Database Design

An unintended inconsistency caused by updating duplicated data in one table while failing to update the corresponding data in another table.

An update anomaly occurs when the same fact is duplicated across related tables and a change reaches one copy but not another. The database then contains inconsistent data. section 2.3 section 3.3

Product and review tables

Suppose product and review tables share product_id and manufacturer. Changing a product’s manufacturer requires updating both tables. Updating only product leaves new manufacturer data in one table and old data in the other. section 2.3

Prevent the anomaly with these practical design rules:

• Keep related tables from sharing columns beyond their primary and foreign keys. section 2.3
• Normalize the design by separating each entity or concept into an appropriate table. section 6.2
• Remove transitive dependencies by placing indirectly dependent data in a separate table and linking it with a foreign key. section 6.7

Use the figure to compare duplicated manufacturer values before and after an incomplete update. section 2.3

How Grokking Relational Database Design covers it

How deep it goes

Explains the failure with a manufacturer example and connects prevention to normalization and transitive-dependency removal.

Examples

Uses product and review tables sharing manufacturer, then uses employee and department data to show transitive update problems.

What it assumes

Assumes familiarity with related tables, primary keys, foreign keys, and columns while reinforcing their design role.

What you can do after

Evaluate database designs for duplicated facts and apply table decomposition to prevent inconsistent updates.

How it connects

Connects update anomalies with data redundancy, normalization, primary keys, foreign keys, and transitive dependency.

liveBookmanning.com

Read Grokking Relational Database Design on liveBook Get the book at manning.com

Related concepts

• anomalykind of · An anomaly is an observation, behavior, or state that deviates from an expected or typical pattern or condition.
• redundancyproduced by · Redundancy is the presence of duplicate or overlapping information, components, or resources, whether intentional or unnecessary.
• consistencyproduces · Consistency is the property of related data, behavior, outputs, or states agreeing with one another or conforming to defined rules across relevant instances or operations.
• delete anomalycontrasts with · An unintended loss of unrelated entity information caused by deleting a record from a poorly designed table.

This explainer was generated by Manning's knowledge graph from the text of Grokking Relational Database Design. The section links point to the parts of the book it draws on.