Normalization and Relationship Design
Normalization organizes facts to reduce duplication and update anomalies. A one-to-many relationship often stores a foreign key on the many side. A many-to-many relationship typically uses a junction table whose composite primary key contains the two foreign keys. One-to-one relationships can use a unique foreign key, depending on the domain.
Normalization is not an absolute ban on duplication. Carefully chosen denormalization can improve read performance, but it creates synchronization and correctness responsibilities. Start with a clear normalized model, measure real query patterns, and denormalize only with an explicit strategy.
Many-to-many junction table
CREATE TABLE course_enrollments (
course_id BIGINT NOT NULL REFERENCES courses(id),
student_id BIGINT NOT NULL REFERENCES students(id),
enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (course_id, student_id)
);💡 The junction table stores each course-student relationship once and can carry relationship attributes such as enrollment time.
Key Takeaways
- Normalize to reduce redundant facts and update anomalies.
- Use junction tables for many-to-many relationships.
- Denormalize only when measured needs justify the added consistency burden.
Knowledge Check
How is a typical many-to-many relationship represented relationally?