Many-to-Many Relation

[JiaPei Jen]

How do I prepare my database to handle many-to-many relation?

[Wayne L Johnson]

Perhaps the most common method is to create a third table where you store foreign keys into the two tables in the relationship. So if you have STUDENT and CLASS tables, and you want to manage them in a many-to-many relationship, do something like this:

STUDENT table
  StudentID  int
  Name       varchar(64)
     ...
CLASS table
  ClassID    int
  Name       varchar(64)
      ...
STUDENT_CLASS table
  StudentID   int
  ClassID     int

You could add a primary key to the "STUDENT_CLASS" key that is separate from the StudentID/ClassID combination, and possibly have other information (DateCreated, etc). But using a join table like this is the most common approach.