Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Posts
    7

    intersection tables question

    I searched this forum for ideas but could not find an answer. Here is the problem i am dealing with.

    I have three tables: case,address and name. case and address have a many to many relationship and so does name and address. I created two intersection tables: case_name with composite: caseid,nameid and address_name with composite nameid,addressid.

    What i am wondering now is, what about instead of creating two intersection tables, i just create one intersection table with three composite keys: a case_name_address table with a caseid, a nameid and an addressid as a composite. Are there any advantages or disadvantages to using a three key composite primary key?

    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ofilha View Post
    Are there any advantages or disadvantages to using a three key composite primary key?
    the advantages are, if you only have one relationship, in which all three ids have to be present, then this 3-key design nicely accommodates that

    the disadvantages are, if you can have a relationship between case and address, without regard to the name involved, or a relationship between name and address, without a case being involved, then this 3-key design is rubbish for supporting those

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

  3. #3
    Join Date
    Sep 2008
    Posts
    7

    intersection tables

    Thank you.

Posting Permissions

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