A table course_offering(course_id, textbook, instructor) stores one row for every combination of a course's textbooks and its instructors, so a course with 3 textbooks and 2 instructors occupies 6 rows. What problems does that shape create, and how would you restructure it?

## Every row should be one fact In a well-formed table each row asserts a single statement about the world. Read one row of course_offering: (CS101, Algorithms, Dr Chen). What does it mean? Not that Dr Chen uses that book, only that CS101 has that textbook and CS101 has that instructor. The row is the accidental pairing of two independent facts, and the table has no way to record one without the other. ## Why the cross product is forced Suppose CS101 has textbooks A, B, C and instructors X, Y. If you stored only three rows, (A,X), (B,Y), (C,X), a reader could reasonably conclude that book B is tied to instructor Y. To avoid asserting a pairing that does not exist, you must store all 6 combinations, or start putting NULLs in half the columns. Both are bad: the first is redundant, the second breaks the meaning of the key. This is exactly the situation Fourth Normal Form describes, and the formal name for what holds here is a multivalued dependency, course_id determines a set of textbooks independently of the instructors. ## The concrete costs - Write amplification. Adding one textbook is not one insert, it is one insert per instructor. Adding an instructor is one insert per textbook. A course with 10 books and 5 staff is 50 rows for 15 facts. - Partial-write corruption. If the loop that inserts 5 rows fails after 3, the table now claims a specific book/instructor pairing that nobody intended. There is no constraint that can catch this, because the table cannot tell a real pairing from an accidental one. - Deletion anomalies. Removing the last instructor removes every trace of which textbooks the course uses. - Query confusion. SELECT DISTINCT is required everywhere. Any aggregate over the table double counts, for instance summing textbook prices per course gives price times instructor count. ## The restructure Two tables, each with a compound primary key: course_textbook(course_id, textbook) and course_instructor(course_id, instructor). Each is a clean many-to-many junction between a course and one other thing. Every row is one fact, every insert and delete is one row, and a natural join on course_id rebuilds the original table exactly, no rows lost and no rows invented. That exact reversibility is what makes the split safe. ## When the original table is correct If the business actually pairs the two columns, say each instructor picks their own textbook for the course, then (CS101, Algorithms, Dr Chen) is a real three-way fact and there is no cross product to eliminate. The test is empirical: ask whether choosing a textbook constrains which instructor can appear with it. If not, the columns are independent and belong apart. This is why the decision needs domain knowledge; you cannot infer it from a sample of data alone, since a small dataset can look like a cross product by coincidence. ## How to spot it elsewhere The generic smell is a table whose primary key has three or more columns where two of them vary independently. Row counts that jump by a factor when one value is added, aggregates inflated by a constant multiple, and queries that always need SELECT DISTINCT are the practical signals.