Many-to-many relationships examples

by

Example scenario: students and courses at a university. A given student might be on several courses, and naturally a course will usually have many students.

Example tables, simple design:

CREATE TABLE `Student` (
    `StudentID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `FirstName` VARCHAR(25),
    `LastName` VARCHAR(25) NOT NULL,
    PRIMARY KEY (`StudentID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `Course` (
    `CourseID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Code` VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
    `Name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`CourseID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `CourseMembership` (
    `Student` INT UNSIGNED NOT NULL,
    `Course` SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (`Student`, `Course`),
    CONSTRAINT `Constr_CourseMembership_Student_fk`
        FOREIGN KEY `Student_fk` (`Student`) REFERENCES `Student` (`StudentID`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `Constr_CourseMembership_Course_fk`
        FOREIGN KEY `Course_fk` (`Course`) REFERENCES `Course` (`CourseID`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci

Find all students registered for a course:

SELECT
    `Student`.*
FROM
    `Student`
    JOIN `CourseMembership` ON `Student`.`StudentID` = `CourseMembership`.`Student`
WHERE
    `CourseMembership`.`Course` = 1234

Find all courses taken by a given student:

SELECT
    `Course`.*
FROM
    `Course`
    JOIN `CourseMembership` ON `Course`.`CourseID` = `CourseMembership`.`Course`
WHERE
    `CourseMembership`.`Student` = 5678

More Related Contents:

  1. Using PHP data in MySQL [closed]
  2. Gather column name of a table from database [closed]
  3. which is better every user have a table or just a row [closed]
  4. PHP – Change value y,n in database [closed]
  5. PDOException SQLSTATE[HY000] [2002] No such file or directory
  6. Automated or regular backup of mysql data
  7. strange character encoding of stored data , old script is showing them fine new one doesn’t
  8. Show values from a MySQL database table inside a HTML table on a webpage [duplicate]
  9. How can I store and retrieve images from a MySQL database using PHP?
  10. PDO mysql: How to know if insert was successful
  11. How to loop through a mysql result set
  12. Maximum execution time in phpMyadmin
  13. How to successfully rewrite old mysql-php code with deprecated mysql_* functions?
  14. Mechanisms for tracking DB schema changes [closed]
  15. Fetching single row, single column with PDO
  16. Mysql No connection could be made because the target machine actively refused it
  17. Replacing mysql_* functions with PDO and prepared statements
  18. Is there any way to check the performance of mysql Indexing
  19. IP address storing in MySQL database using PHP [duplicate]
  20. How to store an array into mysql?
  21. phpmyadmin automatic logout time
  22. PHP – Secure member-only pages with a login system
  23. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  24. Fatal error: Call to undefined function mysqli_result()
  25. Laravel nested relationships
  26. Selecting all fields except only one field in mysql [duplicate]
  27. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  28. MySQL ORDER BY rand(), name ASC
  29. Auto Increment skipping numbers?
  30. How to get last inserted id from table MySQL [duplicate]

Leave a Comment