SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Liverpool
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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)
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the way this is usually handled is that there is also a quantity column in the junction table

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

    sometimes you will also see a price column in the junction table, representing an "override" of the price in the products table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •