Reducing Redundancy in Database Design
How to identify and eliminate field-level redundancy across database entities using shared reference tables.
It is common practice to define each database entity independently. When multiple entities share common fields, we tend to accept the duplication as long as the overall Entity-Relationship design is sound. However, this redundancy can be eliminated.
The Common Pattern
Consider two tables, Customer and Supplier, that both contain address, city, country, and phone fields. The data is structurally identical, yet it is defined in two separate places.
A Cleaner Approach
Extract the shared fields into a dedicated reference table:
CREATE TABLE ContactInfo (
id INT PRIMARY KEY,
address VARCHAR(255),
city VARCHAR(100),
country VARCHAR(100),
phone VARCHAR(20)
);
Both Customer and Supplier then reference ContactInfo via a foreign key. This approach reduces duplication, ensures consistency when updating shared data, and makes the schema easier to extend.
The same principle applies broadly: any time you see identical field groups appearing across multiple tables, consider whether they can be normalized into a shared structure.