locked
Many to many relationship RRS feed

  • Question

  • Dear everyone,

    I am working on a website which stores records for Domicile Certificates.

    I am stuck at a situation while designing my database.

    First here is the scenario:

    http://picturepush.com/public/6800968

    Use the FUll URL above for the Image


    In the above picture you can see the situation which shows a many to many relationship and the way i have currently designed it is flawed.

    I know I need to add a third table which maps the committee id to the member id but i need some hints on querying that third table, won't the queries to the third table be complex ones in order to find the details for individuals members.

     

    Thanks in advance.





    Saturday, October 22, 2011 2:56 AM

Answers

  • Although my above image represents a many to many relationship but the requirements of the system suggest that a member can not belong to more than one committee so there is a one to many relationship. 

    If i just add a foreign key column CommitteeID to the MemberDetails table which references the committee table, will it solve the issue.


    A foreign key on each of the MemberID columns of the dbo.DomicileCommittee table will ensure that only valid members may be part of a committee. However, it will not prevent the same member from belonging to more than one committee. 

    With a one-to-one relationship, you don't need the association table.  Instead, you can add CommitteeID to dbo.CommitteeMembersDetails along with a foreign key to dbo.DomicileCommittee.  This will guarantee that a member can belong to exactly one valid committee while allowing a committee to have zero or more members.  If you can have members that are not yet associated with a committee, allow NULL CommitteeID values instead of NOT NULL in the script below.  You may also want a non-unique index on CommitteeID for performance.

    ALTER TABLE dbo.CommitteeMembersDetails
    	ADD CommitteeID int NOT NULL;
    	
    ALTER TABLE dbo.CommitteeMembersDetails
    	CONSTRAINT FK_CommitteeMembersDetails_DomicileCommittee 
    	FOREIGN KEY(CommitteeID) REFERENCES dbo.DomicileCommittee;
    

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Stephanie Lv Monday, October 24, 2011 7:24 AM
    • Marked as answer by Stephanie Lv Saturday, October 29, 2011 5:58 AM
    Saturday, October 22, 2011 3:30 PM
    Answerer

All replies

  • I know I need to add a third table which maps the committee id to the member id but i need some hints on querying that third table, won't the queries to the third table be complex ones in order to find the details for individuals members. 

     

    A best practice in database design is to eliminate repeating attributes (FirstMemeberID, SecondMemberID, etc.).  I suggest you remove the MemberID columns from the DomicileCommittee table and create an association table with CommitteeID and MemberID.  That will also allow you to have committees with more than 5 members.  I think a query to list all committee members regardless of the number of members is fairly simple.  Untested example:

     

    CREATE TABLE dbo.CommiteeMember(
    	CommitteeID int NOT NULL
    	,MemberID int NOT NULL
    	,CONSTRAINT PK_CommiteeMember PRIMARY KEY(CommitteeID, MemberID)
    	,CONSTRAINT FK_CommiteeMember FOREIGN KEY(CommitteeID) REFERENCES dbo.DomicileCommittee
    	,CONSTRAINT FK_CommiteeMembersDetails FOREIGN KEY(MemeberID) REFERENCES dbo.CommitteeMembersDetails
    	);
    GO
    
    SELECT *
    FROM dbo.DomicileCommittee AS dc
    JOIN dbo.CommitteeMembers AS cm ON
    	cm.CommitteeID = dc.CommitteeID
    JOIN dbo.CommitteeMembersDetails AS cmd ON	
    	cmd.MemberID = cm.MemberID;
    GO
    

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Warwick Rudd Saturday, October 22, 2011 12:14 PM
    Saturday, October 22, 2011 3:30 AM
    Answerer
  • Thanks i will try them, and if i get stuck again, will get here back.

     

     

    Saturday, October 22, 2011 3:36 AM
  • Although my above image represents a many to many relationship but the requirements of the system suggest that a member can not belong to more than one committee so there is a one to many relationship.

     

    If i just add a foreign key column CommitteeID to the MemberDetails table which references the committee table, will it solve the issue.

    Waiting for your response.

    Saturday, October 22, 2011 1:38 PM
  • Although my above image represents a many to many relationship but the requirements of the system suggest that a member can not belong to more than one committee so there is a one to many relationship. 

    If i just add a foreign key column CommitteeID to the MemberDetails table which references the committee table, will it solve the issue.


    A foreign key on each of the MemberID columns of the dbo.DomicileCommittee table will ensure that only valid members may be part of a committee. However, it will not prevent the same member from belonging to more than one committee. 

    With a one-to-one relationship, you don't need the association table.  Instead, you can add CommitteeID to dbo.CommitteeMembersDetails along with a foreign key to dbo.DomicileCommittee.  This will guarantee that a member can belong to exactly one valid committee while allowing a committee to have zero or more members.  If you can have members that are not yet associated with a committee, allow NULL CommitteeID values instead of NOT NULL in the script below.  You may also want a non-unique index on CommitteeID for performance.

    ALTER TABLE dbo.CommitteeMembersDetails
    	ADD CommitteeID int NOT NULL;
    	
    ALTER TABLE dbo.CommitteeMembersDetails
    	CONSTRAINT FK_CommitteeMembersDetails_DomicileCommittee 
    	FOREIGN KEY(CommitteeID) REFERENCES dbo.DomicileCommittee;
    

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Stephanie Lv Monday, October 24, 2011 7:24 AM
    • Marked as answer by Stephanie Lv Saturday, October 29, 2011 5:58 AM
    Saturday, October 22, 2011 3:30 PM
    Answerer