'Is it a good design in RDBMS to reference RelationshipTable ID from another table
Consider the following example
CREATE TABLE courses (
id INT,
title VARCHAR(100),
);
CREATE TABLE students (
id INT,
name VARCHAR(100),
);
Because the same student can have many courses and the same course can be taken by many students there is a many-to-many relationship so we create a relationship table
CREATE TABLE courses_has_students (
id INT PRIMARY KEY,
course_id REFERENCES courses (id),
student_id REFERENCES students (id),
);
The question is where to store the information about exam grades.
- Is it better to have table
gradesthat reference the courses_has_students table;
CREATE TABLE grades (
courses_has_students REFERENCES courses_has_students (id),
grade INT,
);
- Is it better to have table
gradesthat reference both course and students' table;
CREATE TABLE grades (
course_id REFERENCES courses (id),
student_id REFERENCES students (id),
grade INT,
);
- Is it better to add a new column
gradefor the course_has_students
CREATE TABLE courses_has_students (
id INT PRIMARY KEY,
course_id REFERENCES courses (id),
student_id REFERENCES students (id),
grade INT
);
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
