2 Replies - 774 Views - Last Post: 03 April 2010 - 11:51 AM Rate Topic: -----

#1 thacoolestn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 10-April 09

How to create a Relationship table that 2 of the same tables

Posted 02 April 2010 - 01:43 PM

I am trying to create a Relatinship Set that References a Many to Many Mapping with the the two Entities Sets being the same..for example


CREATE TABLE Trust(
{
login char(10)
login char(10)
rating int

primary key(login,login);

Foreign key (login) REFERENCES user_Profile;
Foreign key (login) REFERENCES user_Profile;
}


This Table is used for one User rating another User and holding the rating score.. I see the ambiguity but how can i specify different logins; if they are both primary keys of my user_Profile table??

I appreciate the help!

Is This A Good Question/Topic? 0
  • +

Replies To: How to create a Relationship table that 2 of the same tables

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,127
  • Joined: 18-April 07

Re: How to create a Relationship table that 2 of the same tables

Posted 02 April 2010 - 02:00 PM

You have your users table which lists all your users. You then have a second table which features the ID field (primary key), you have a second field like "reviewerID" which is a foreign key back to the user's primary key and represents the user's ID of who is giving the rating and then you have a "reviewedID" which is another foreign key which links to the user's primary key and lastly the rating field. The two foreign keys together form a "compound key" (aka composite key).

users
---------
ID (Pk)
name
address
...


ratings
---------
ID (PK)
reviewerID (FK connects to ID in users table)
reviewedID (FK connects to ID in users table)
rating



The two fields reviewerID and reviewedID fields in the ratings table will be a compound key thus guaranteeing that they are unique since only one reviewer can give another user a single rating once. This is a many users to many users relationship. Hope I have explained this thoroughly enough.

:)
Was This Post Helpful? 0
  • +
  • -

#3 thacoolestn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 10-April 09

Re: How to create a Relationship table that 2 of the same tables

Posted 03 April 2010 - 11:51 AM

View PostMartyr2, on 02 April 2010 - 01:00 PM, said:

You have your users table which lists all your users. You then have a second table which features the ID field (primary key), you have a second field like "reviewerID" which is a foreign key back to the user's primary key and represents the user's ID of who is giving the rating and then you have a "reviewedID" which is another foreign key which links to the user's primary key and lastly the rating field. The two foreign keys together form a "compound key" (aka composite key).

users
---------
ID (Pk)
name
address
...


ratings
---------
ID (PK)
reviewerID (FK connects to ID in users table)
reviewedID (FK connects to ID in users table)
rating



The two fields reviewerID and reviewedID fields in the ratings table will be a compound key thus guaranteeing that they are unique since only one reviewer can give another user a single rating once. This is a many users to many users relationship. Hope I have explained this thoroughly enough.

:)


I understand that..but what is the exact synxtax when im referencing from example (FK connects to ID in user table)..the only syntax i know is FOREIGN KEY (ID) REFERENCES USER.. which means that ID is an attribute in both.. i dont know how to reference it when the Relationship Table and the Key from the User table are different
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1