Students can enroll in multiple courses, courses have multiple students. Building the warehouse model, a bridge table between fact_enrollment and dim_course/dim_student, or denormalize course info directly onto the enrollment fact?
Accepted answer
A proper bridge or associative table, fact_enrollment as the bridge itself with keys to dim_student and dim_course, is the standard approach for many-to-many. It avoids the row multiplication and update anomalies denormalizing would introduce.
Denormalizing course attributes onto the enrollment fact only makes sense for a small number of stable, frequently-queried attributes as a query-convenience layer on top of the properly modeled bridge, not as a replacement for it.
Start with the execution plan, numbers beat guesses.
Sign in to reply.
