0

Hi I'm a student and learning SQL. I have table Student and table Subjects

-Student(ID,name,subjects)
(1,testing,here is the id of multiple subject)

-Subjects (ID,name,Desc)
(1,Subject1,somedesc)
(2,Subject2,somedesc)
(3,Subject3,somedesc)
(4,Subject4,somedesc)

so I want to store the multiple entry of subjects into 1 entry of student. any idea how can I make it? If not possible, what would you recommend?

Mike
  • 4,041
  • 6
  • 20
  • 37
Ranz
  • 21
  • 3
  • 1
    You need to create 3rd table which stores Student table id and subject Id. it is many to many relationship. ex. stud_sub(ID, stud_id,sub_id). – Sachin Sep 20 '18 at 06:27
  • Have a read of https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Nigel Ren Sep 20 '18 at 06:30
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – s__ Sep 20 '18 at 06:54

2 Answers2

1

I don't recommend your current approach, because you are trying to store comma separated, unnormalized, data in the Student table, where there is one record with a CSV list of subjects. If someone were to give you an exact answer using this design, the query would be fairly ugly, and inefficient. Instead, I recommend the following schema:

Student (ID, name)    -- and maybe other student metadata
Subjects (ID, name, description)
Student_Subjects (ID, StudentID, SubjectID)

Here, we store the relationship between one student and his subjects using multiple rows in the table Student_Subjects. By the way, Student_Subjects is often called a "junction" or "bridge" table, because it connects two types of a data, in a clean and normalized way.

Now, if you want to persist a given student's subjects, you can use a single insert, something like:

INSERT INTO Student_Subjects (StudentID, SubjectID)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (1, 4);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

You can use Foreign key constraint. In your example, consider Student as parent table and Subjects as child table. 

Student (id, name)
Subjects (id, student_id, name, desc)

The foreign key of Student table referring the subjects is, student_id column in Subjects table.

Praveen
  • 1
  • 1