How Bridge Tables Solve Many-to-Many Relationships in Kimball Data Modeling

In dimensional data modeling, most fact tables assume a clean one-to-many relationship, but real-world scenarios like insurance claims often involve multiple diagnosis codes per record, creating a genuine many-to-many problem. A common but flawed approach is adding multiple fixed columns — such as diagnosis_1, diagnosis_2, diagnosis_3 — directly to the fact table, which breaks as soon as the number of values exceeds the column count. This structure also forces analysts to repeat filter logic across every column, making queries brittle and prone to silent errors when new columns are added later. Ralph Kimball addressed this problem using a healthcare billing example, introducing what he called a Diagnosis Group table — now commonly known as a bridge table — to properly model the relationship. The bridge table pattern sits between the fact and dimension tables, allowing an unlimited number of dimension values to link to a single fact row without duplicating or distorting measures like billed amounts.