Here is a situation I'm on the fence about and that is many-to-many "relationship".
Table: Students (PK_StudentID)
Table: Teachers (PK_TeacherID)
Table: StudentsTeachers(PK_StudentsTeachersID, FK_StudentID, FK_TeacherID), Unique Contraint on (FK_StudentID, FK_TeacherID)
*Let's not let semester or school year cloud the issue.
Does the StudentsTeachers table really need the PK_StudentsTeachersID? All of these id's have no intelligence added to them. They're just unique numbers. Is there a need to create any part of the student record without indicating the teacher? If you need to join this table to a StudentTeacherMeeting table, having the one field for a join is always nice, but then you require the join just to get a list of those meetings for a particular teacher.
Speed, select flexibility, join compliations, memory, disk space, you just factor this stuff on a case by case basis.