The consensus, on web forums at least, is that junction tables when dealing with many to many relationships should not use a single, auto incrementing, primary key since the 2 keys that comprise the table form a distinct key themseleves. Which is fine for the sample below.

Student (stud_id, name)
1 Dave
2 Jan
3 Paul

Module (module_id, module)
OOP1 OOP
AI1 AI
HCI1 HCI

Registered (stud_id, module_id)
1 OOP1
1 AI1
2 OOP1
3 OOP1
3 AI1

This is because in this example, many students can register for 1 module. And 1 module can have many students registered to it. But a student can only register for a module once.


But what about a situation as follows in which a customer may order the same item many times on a single order...

order(order_id, customer)
1 J.Simmons
2 Billy Bob
3 Travis
4 Travis
5 Travis
6 D. Smith

product(product_id, name)
TV1 Widescreen TV
TV2 Gas Plasma
DVD1 Panasonic DVD Player

order_item(order_id, product_id)
1 TV1
1 TV2
1 TV1
2 DVD1
4 DVD1
5 DVD1
5 DVD1
5 DVD1

In the above example, a single customer might order the same product many times on a single order all differentiated by, for instace, a customer order reference, or a specific delivery address or whatever.

In this instance, the combination of keys in the junction table is no longer unique. Is it acceptable to create an auto increment primary key in this instance?