Results 1 to 2 of 2
Thread: Keys in Junction Tables
Jan 18, 2005, 08:44 #1
- Join Date
- Jan 2005
- 0 Post(s)
- 0 Thread(s)
Keys in Junction Tables
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)
Module (module_id, module)
Registered (stud_id, module_id)
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...
2 Billy Bob
6 D. Smith
TV1 Widescreen TV
TV2 Gas Plasma
DVD1 Panasonic DVD Player
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?