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
    teacherid
    subject
    clasid



    Class


    Classid
    teacherid
    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

    TeachersClasses

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

    So the TeachersClasses

    Has 2 columns

    teacherid
    Classid

    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
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  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