Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    24

    Help on a couple of tables for ERD

    Designing an ERD for a pet shop for school that does boarding and grooming. On the ERD we have the pet table with "pet_id" that has a many to many relationship with the color table that has colors and an attribute "color_id". Between them we have a composite table or "junction table" with both pet and color id as Foreign Keys and an artifical petcolor_id field as an Primary Key. Do we need this made up petcolor_id primary key field in there?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Datanalysis
    Do we need this made up petcolor_id primary key field in there?
    excellent question, and the answer is absolutely not

    the PK of the junction table should be a composite PK consisting of the two FKs

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    24
    Thank you for your response. Yes currently that is how I have the junction table 1 primary key with 2 foreign keys, so this correct? Or do I just need 2 foreign keys that would cross reference each other for pet and color?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the junction table will have only 2 columns

    (i prefer to call it a relationship table)

    each of the 2 columns is a FK

    together, the two columns are the PK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2008
    Posts
    24
    what happens if I leave that PK in there? when it comes to programming etc, more work

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in that case you will need to define a separate UNIQUE constraint on the pair of FKs, to ensure that you don't link the same pet to the same colour more than once

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    And you'll also have remember to mark both columns as non nullable

    ...or this will be valid input
    Code:
    +------+------+------+
    | skey | col1 | col2 |
    +------+------+------+
    | 1    | ABC  | DEF  |
    | 2    | NULL | DEF  |
    | 3    | ABC  | NULL |
    | 4    | NULL | XYZ  |
    | 5    | NULL | NULL |
    +------+------+------+
    George
    Home | Blog

Posting Permissions

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