CODEWITHSUNDEEP
sqldatabasepostgresql
MH Sagar
MH Sagar

Reputation: 136

How can I establish relation between two tables

Suppose I have a table subject in my database with data like:

subject_nam credit  gpa
chemistry       3.00    null
physics         3.00    null

And also suppose I have a student table. It will contain basic student information.

Now what relation do I need to have a copy of these subject for each student. gpa column would be different for every student.

Upvotes: 0

Views: 47

Answers (1)

The Impaler
The Impaler

Reputation: 48850

I would create a database model such as:

create table subject (
  id int primary key not null,
  name varchar(50) not null,
  credits int not null
);

create table student (
  id int primary key not null,
  name varchar(50) not null
);

create table gpa_score (
  subject_id int not null,
  student_id int not null,
  score int not null,
  weighted_score int not null, -- added extra column as requested
  constraint pk_gpa primary key (subject_id, student_id),
  constraint fk1 foreign key (subject_id) references subject (id),
  constraint fk2 foreign key (student_id) references student (id)
);

Upvotes: 1

Related Questions