SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    many-to-many modify

    If I have a table for Mechanics, which could be any mechanic in the US., with each one doing many types of work on cars. I take this to be a many to many relationship [MechId from registered 'Mechanics' table and WorkId from a 'Workcategory' table]

    MechWork
    MechID | WorkID

    But what if I want to include one other attribute, say list whether or not certified for each type of work by adding a column after WorkID?

    Should this table now have a primary index column?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    interesting question!!

    you can simply add the "certified" column to the many-to-many table --

    create table MechWork
    ( MechID integer not null
    , WorkID integer not null
    , certified char(1)
    )

    the purpose of a primary key is to allow each row to be uniquely identified

    the best primary key for this table is the first two columns!

    create table MechWork
    ( MechID integer not null
    , WorkID integer not null
    , certified char(1)
    , primary key (MechID, WorkID)
    )

    since primary keys must be unique, this also gives you the added benefit that you will never be able to certify the same mechanic on the same work more than once

    but the reason this was an interesting question is because the composite primary key should also be declared even when there is no additional column like "certified" in the table!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nice. thanks r937


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
  •