determine a database relationship

Question

I need help to decide if the relationship between this 2 table is one to many or many to many. One student can sign up for many class and one class can have multiple student. This is my table below:

Thankyou

Normalize your structures first. Holding `class_student_id` as csv list is a bad idea. Use **[Junction table](https://en.wikipedia.org/wiki/Junction_table)** instead(**[example](http://www.thinkingcog.com/image.axd?picture=clip_image002_thumb.gif)**) — Lukasz Szozda, Jan 03 '16 at 08:30
see **[this question](http://stackoverflow.com/questions/34553775/mysql-select-query-to-fetch-record-base-on-list-values/34553991#34553991)** for how to fix your structure — amdixon, Jan 03 '16 at 08:34

score 1 · Accepted Answer · answered Jan 03 '16 at 10:12

This is how it should be:

classes to students is in HABTM Relationship maped by classes_students. Below is schema:

CREATE TABLE `students` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE `classes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `subject` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE `classes_students` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `class_id` INT(11) NOT NULL,
    `student_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK__classes` (`class_id`),
    INDEX `FK__students` (`student_id`),
    CONSTRAINT `FK__classes` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`),
    CONSTRAINT `FK__students` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
)
COMMENT='table to support HABT classes to students'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

How do I add a student into a class? Should I insert a new record to class table then I can add a student into class_student table? Or is there other way . Sorry, I'm really new to junction table !' — John, Jan 03 '16 at 12:56
just create a new entry in classes_students with student_id and class_id where you want the put the students to. this is all. to remove a student from a class you just have to find and delete record from classes_students table only. first create a class(if not exists) and then student(again if not exist) and then add entry to classes_students. — Ravi S. Singh, Jan 03 '16 at 13:39

score 0 · Answer 2 · answered Jan 03 '16 at 08:41

0

This is a many-to-many relationship. You need to have Students and Classes into separate tables and connect them via a junction table which will contain a student_id and a class_id.

answered Jan 03 '16 at 08:41

alextsil

504
3
11
26

What if a class have other fields such as teacher , subject , do I add them into junction table or class table – John Jan 03 '16 at 08:44
If you need to have teachers in your system you should create a table which holds the teachers. Then you connect the teacher to the class depending on their relationship (one or many teachers per class). For the subjects, if you just need a title you can put it in the class as a field, but if you need a complete subjects with more info, you need to create a table for them. – alextsil Jan 03 '16 at 08:49

determine a database relationship

2 Answers2