SQL Server Performance

best way to implement many to many relationship?

Discussion in 'SQL Server 2005 General Developer Questions' started by rohitkochar, Jun 11, 2007.

  1. rohitkochar New Member

    HI ,
    What is the best way to implement many to many relationhip in sql server??
    Suppose i have two tables teachers and class .
    A class can have many teachers and a teacher can teach to many class .
    What is the best way to implement this ?



    teacher name

    Is this the best way to implement this ??
    Tell me any other way to implement this ??

    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida
  2. twoboats New Member

    A link table


    1 to many Teacher to TeachersClasses
    many to 1 TeachersClasses to Classes

    So the TeachersClasses

    Has 2 columns


    and the unique key is composite of both columns

    Tradeco wholesalers, drop shippers & trade network
  3. Adriaan New Member

    If there can be only one teacher teaching the given class, then add the TeacherId to the Class table.<br /><br />If there can be multiple teachers teaching a given class (in parallel or together - doesn't really matter) then add a cross-reference table:<br /><br />Teacher (TeacherId, TeacherName)<br /><br />Class (ClassId, Subject)<br /><br />ClassTeacher (ClassId, TeacherId, &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />lus other details concerning this teacher teaching this class&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' />
  4. dineshasanka Moderator

    link table is the easiest and simple way of doing it

    Contributing Editor, Writer & Forums Moderator

    Visit my Blog at
  5. rohitkochar New Member

    thanks to all...i think link table is the best solution to my problem..

    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida

Share This Page