DB many-to-many: use "association table"

In response to questions about implementing many-to-many relationships in a relational database, I have been working with DB2 mainframe databases for over 18 years. Many-to-many relationships frequently arise, for example students enrolled in a given course each semester. Any one student will have several courses in a semester and each course given in that semester will have several students enrolled in it.

The usual solution for these situations is to have what is generically called an "association table". Each row in the association table will have two foreign keys, one foreign key pointing to student# in a Students table and the other foreign key pointing to course# in a Courses table. The primary key for this Enrollments table would be the composite key of these two foreign keys. Each row in this Enrollments table would have separate columns for attributes (properties) such as seat number and final grade, which are different for each student in each course.

On the other hand, attributes such as room location or cell phone would belong either to the parent Students table or to the parents Courses table. For example, our Intensive Web Development is held in Room 202 of 48 Cooper Square whether anybody enrolls in the course or not. So room# belongs in the Courses table not the Enrollments table. My cell phone# is completely independent of whatever courses I take. So cell phone# belongs in the Students table not the Enrollments table.