locked
Table Relationship Question RRS feed

  • Question

  • User407597441 posted

    I have two tables that need to have a unique relationship and I don't know how to make this work

    Here is the list of tables:

    Contact

    ContactID, Firstname, LastName

    RelatedContact

    RelatedContactID, ContactID, RelatedID, ContactRole

    Contact.ContactID 1-many on RelatedContact.ContactID

    I also want to have a relationship or some sort of reference on RelatedContact.RelatedID back to Contact.ContactID.  If I try to make a relationship in sql server management studio database diagram, it forces a one to many from Contact.ContactID to RelatedContact.RelatedID.  This is not what I want since that relationship will create the same one that was already created when I setup the ContactID field on both tables.  What I am trying to do is create a many to many relationship, but with using the same table on each side of the many to many.

    Can someone please help?  Thank You

    Tuesday, May 5, 2015 4:39 PM

Answers

  • User1711366110 posted

    I also want to have a relationship or some sort of reference on RelatedContact.RelatedID back to Contact.ContactID.  If I try to make a relationship in sql server management studio database diagram, it forces a one to many from Contact.ContactID to RelatedContact.RelatedID.  This is not what I want since that relationship will create the same one that was already created when I setup the ContactID field on both tables.  What I am trying to do is create a many to many relationship, but with using the same table on each side of the many to many

       As per my understanding of this case, I have shared my ideas below :
    1.If you need one-Many relationship then just remove the column "RelatedID" from table "RelatedContact" like below :

    ContactTable
    --------
    ContactID -->primary key 
    Firstname, 
    LastName
    
    RelatedContactTable
    ---------------
    RelatedContactID, -->primary key
    ContactID, -->foreign key of contact table
    RelatedID, -->remove this column
    ContactRole
    

    2,If you need Many-many relationship ,you can try like below :

    ContactTable
    --------
    ContactID -->primary key 
    Firstname, 
    LastName
    
    RoleTable
    ----------
    RoleId -->Primary key
    ContactRole
    RelatedContactTable --------------- RelatedContactID int identity(1,1), ContactID, -->foreign key of ContactTable
    RoleId,-->foreign key of RoleTable
    primary key(contactId,RoleId) -->composite key

    --or without composite key like below--

    RelatedContactTable
    ---------------
    RelatedContactID -->primary key
    ContactID, -->foreign key of ContactTable
    RoleId,-->foreign key of RoleTable

    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 6, 2015 4:34 AM

All replies

  • User-1618234021 posted

    If you want to maintain many-to-many relationship between these 2 tables, you need to create a third table in between. As many to many has to be implemented that way by breaking it down to multiple one to many relationships. So your tables would like the following:

    Contact

    ContactID[pk], Firstname, LastName

    RelatedContact

    RelatedContactID[pk], RelatedID, ContactRole

    ContactRelatedContact

    ContactID[fk], RelatedContactID[fk]     and also make these 2 aas composite primary key.

    Wednesday, May 6, 2015 2:44 AM
  • User1711366110 posted

    I also want to have a relationship or some sort of reference on RelatedContact.RelatedID back to Contact.ContactID.  If I try to make a relationship in sql server management studio database diagram, it forces a one to many from Contact.ContactID to RelatedContact.RelatedID.  This is not what I want since that relationship will create the same one that was already created when I setup the ContactID field on both tables.  What I am trying to do is create a many to many relationship, but with using the same table on each side of the many to many

       As per my understanding of this case, I have shared my ideas below :
    1.If you need one-Many relationship then just remove the column "RelatedID" from table "RelatedContact" like below :

    ContactTable
    --------
    ContactID -->primary key 
    Firstname, 
    LastName
    
    RelatedContactTable
    ---------------
    RelatedContactID, -->primary key
    ContactID, -->foreign key of contact table
    RelatedID, -->remove this column
    ContactRole
    

    2,If you need Many-many relationship ,you can try like below :

    ContactTable
    --------
    ContactID -->primary key 
    Firstname, 
    LastName
    
    RoleTable
    ----------
    RoleId -->Primary key
    ContactRole
    RelatedContactTable --------------- RelatedContactID int identity(1,1), ContactID, -->foreign key of ContactTable
    RoleId,-->foreign key of RoleTable
    primary key(contactId,RoleId) -->composite key

    --or without composite key like below--

    RelatedContactTable
    ---------------
    RelatedContactID -->primary key
    ContactID, -->foreign key of ContactTable
    RoleId,-->foreign key of RoleTable

    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 6, 2015 4:34 AM