How to model many to many relationships

Short answer

A many-to-many relationship is modeled with a junction (associative) table that contains foreign keys to both related tables, often with a composite primary key.

Steps

1. Create the two main tables: students and courses.
2. Create a junction table: enrollments with student_id and course_id.
3. Add foreign keys in the junction table referencing both main tables.
4. Use a composite primary key on (student_id, course_id) to prevent duplicates.
5. Add additional attributes to the junction table if needed, such as enrollment date.

Tips

• Name junction tables with a clear, plural form that describes the relationship.
• Consider surrogate keys if the junction table accumulates many columns or needs independent referencing.

Common issues

• Storing many-to-many data directly in one of the main tables violates normalization and creates update anomalies.
• Missing unique constraints on the junction table allows duplicate relationship rows.r