How to Reduce Data Redundancy in a Database

Reduce data redundancy by storing each independently maintained fact in the appropriate table and linking related rows with keys. Normalization helps identify facts that are unnecessarily repeated and separates them without losing the relationships needed to reconstruct the original information.

Repeated values are not automatically a design problem. Two parts can both be described as nut, and many orders can reference the same customer. The important question is whether a fact must be maintained in multiple places. If changing one part's description requires updating several supplier rows, the design stores that fact redundantly.

This article uses Part and SupplierPart examples to distinguish repeated values from redundant facts, explain the resulting anomalies, and demonstrate a decomposition with SQL.

These three situations need different remedies:

• Repeated value: the same text or number appears in different facts. Two different parts may share a description. No correction is needed merely because their descriptions match.
• Redundant fact: the same fact is stored repeatedly. The description of part P1 appears once for every supplier that supplies P1. Normalization can give that description a single place to be maintained.
• Duplicate row or business record: the same supplier-part association is recorded twice when the business rule permits only one such association. Appropriate keys or unique constraints prevent this duplication.

A surrogate ID can make every row's identifier unique while still allowing duplicate business records. Conversely, a repeated foreign-key value can be entirely correct: different suppliers need to reference the same part.

Example 1: Matching descriptions do not imply redundancy

Assume that partNo identifies a part and each part has one current description. Different part numbers may have the same description.

Part: original descriptions and a hypothetical missing value

partNo	(a) Original partDescription	(b) After removing P2's description
P2	nut	— (missing)
P1	bolt	bolt
P3	washer	washer
P4	nut	nut

The facts P2 → nut and P4 → nut concern different parts. Removing P2's description loses information: knowing that P4 is a nut does not establish what P2 is. The repeated word nut therefore does not justify deleting either value or combining the two parts.

The relevant functional dependency is partNo → partDescription. It states that a particular part number determines its description. The reverse dependency does not hold: a description does not uniquely identify a part.

Example 2: Repeating a part description for each supplier

Now consider a table that records which suppliers supply which parts. Assume each supplier-part pair appears at most once, and a part's description is independent of its supplier.

SupplierPart: the same part fact stored for two suppliers

supplierNo	partNo	(a) Original partDescription	(b) After removing one description
S2	P1	bolt	bolt
S7	P6	bolt	bolt
S2	P4	nut	nut
S5	P1	bolt	— (missing)

Both the S2/P1 and S5/P1 rows repeat P1 → bolt. If P1's description changes, both rows must be updated. Updating only one leaves conflicting descriptions for the same part.

The missing-value example illustrates that the description repeats an existing fact. Leaving selected cells empty is not normalization. It would make queries depend on whichever row still contains a description, and deleting that row could remove the only remaining copy.

In this combined SupplierPart design, the part and its supplier association have different lifecycles:

• Update anomaly: changing P1's description requires finding every row for P1. A missed row creates disagreement.
• Insertion anomaly: a new part cannot be recorded with its description until it has a supplier, if both identifiers are required by the combined key.
• Deletion anomaly: deleting a part's last supplier association also deletes its description, even when the part should remain in the catalog.

The problem follows from the business rules, not from the sample's size. With candidate key (supplierNo, partNo), the non-key description depends on partNo alone. This partial dependency violates second normal form.

Store descriptions in Part and supplier-part associations in SupplierPart. The following tables decompose Example 2 only; the first example is a separate demonstration.

Part after decomposition - primary key: partNo

partNo	partDescription
P1	bolt
P6	bolt
P4	nut

SupplierPart after decomposition — primary key: (supplierNo, partNo)

supplierNo	partNo
S2	P1
S7	P6
S2	P4
S5	P1

P1 still appears twice in SupplierPart because two suppliers supply it. These references preserve two distinct associations. Its description now appears in one Part row, so changing it requires one update.

The decomposition is lossless under partNo → partDescription: joining each association to its one matching Part row reconstructs the original supplier, part, and description facts without inventing associations. A foreign key ensures that an association cannot reference a missing part.

SQL implementation

This example uses conventional SQL constraints. Supplier is included so both association identifiers have parent tables. Adapt identifier lengths and any database-specific details to your application.

CREATE TABLE Part (
    partNo VARCHAR(10) NOT NULL,
    partDescription VARCHAR(100) NOT NULL,
    CONSTRAINT pk_part PRIMARY KEY (partNo)
);

CREATE TABLE Supplier (
    supplierNo VARCHAR(10) NOT NULL,
    CONSTRAINT pk_supplier PRIMARY KEY (supplierNo)
);

CREATE TABLE SupplierPart (
    supplierNo VARCHAR(10) NOT NULL,
    partNo VARCHAR(10) NOT NULL,
    CONSTRAINT pk_supplier_part PRIMARY KEY (supplierNo, partNo),
    CONSTRAINT fk_supplier_part_supplier FOREIGN KEY (supplierNo)
        REFERENCES Supplier (supplierNo),
    CONSTRAINT fk_supplier_part_part FOREIGN KEY (partNo)
        REFERENCES Part (partNo)
);

The composite primary key prevents duplicate supplier-part pairs. It does not prevent S2 from supplying multiple parts or multiple suppliers from supplying P1. A unique constraint on partDescription would be inappropriate because different parts may legitimately share a description.

Retrieve the combined information with a join:

SELECT sp.supplierNo, sp.partNo, p.partDescription
FROM SupplierPart AS sp
JOIN Part AS p ON p.partNo = sp.partNo
ORDER BY sp.supplierNo, sp.partNo;

A description may repeat in this query's output without being redundantly maintained in the underlying tables. A report is allowed to repeat information to make its rows understandable.

Before adding a key to existing data, check whether the proposed business identifier is already duplicated. For a legacy table named SupplierPartLegacy:

SELECT supplierNo, partNo, COUNT(*) AS pairCount
FROM SupplierPartLegacy
GROUP BY supplierNo, partNo
HAVING COUNT(*) > 1;

This finds repeated supplier-part pairs, not necessarily identical full rows. Investigate disagreements before removing records. If separate deliveries or contracts are represented, the pair may not be the correct key; those events need their own identity.

Check conflicting descriptions separately:

SELECT partNo
FROM SupplierPartLegacy
GROUP BY partNo
HAVING COUNT(DISTINCT partDescription) > 1;

This flags multiple distinct non-null descriptions for one part. Check null descriptions separately, and remember that text comparisons follow the database's collation rules. A clean result does not prove a dependency: confirm the rule with the people who own the data.

SELECT DISTINCT removes duplicate combinations from query output. It neither deletes stored records nor fixes the schema. Prefer primary-key or unique constraints for ordinary uniqueness enforcement rather than a custom trigger that first searches for a matching row.

A practical process for reducing redundancy

1. Define what each row represents. Identify the entity, association, or event and its candidate keys.
2. Document dependencies. Determine which attributes depend on which identifiers. Do not infer rules solely from today's sample.
3. Resolve inconsistent source data. Establish the authoritative description before moving repeated facts into a parent table.
4. Decompose without losing information. Separate independently maintained facts and verify that joins reconstruct the required associations.
5. Enforce the rules. Add appropriate primary keys, unique constraints, foreign keys, and nullability requirements.
6. Validate the migration. Check associations, missing parents, conflicting facts, and application behavior before retiring the old representation.

First normal form does not imply that a table contains redundant facts. Second normal form removes partial dependencies of non-prime attributes on candidate keys; third normal form further restricts functional dependencies. For definitions and examples, see functional dependencies and achieving second normal form.

When intentional redundancy may be useful

Materialized summaries, caches, and reporting tables may deliberately repeat or derive information to meet measured workload needs. Such designs need a documented source of truth and a reliable refresh or synchronization process. Normalization does not guarantee faster queries, and denormalization does not guarantee better overall performance.

Also distinguish redundant current facts from historical facts. An order line's price at purchase time may legitimately differ from the product's current price; preserving that historical value serves a different business requirement.

The goal is to avoid maintaining the same fact independently in several places. Keep necessary references and distinct facts, even when their values match. In the supplier-part example, store the description once per part and preserve every supplier-part association.

Implementation reference: PostgreSQL documentation on primary-key, unique, and foreign-key constraints.