1

Is it best to create third table with primary key of each table as foreign key ? or add foreign key to first table? For example student table and project table each project has many students.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
programmer
  • 109
  • 1
  • 7
  • 1
    You dont need 3rd table if it is one to many, if it was many to many you would. You just put a FK in the projects table to the StudentID in the student table – Brad Apr 12 '18 at 17:04
  • Possible duplicate of [one to many relationship in database - design concept](https://stackoverflow.com/questions/10145321/one-to-many-relationship-in-database-design-concept) – Tab Alleman Apr 12 '18 at 17:57
  • Another duplicate: https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de – Tab Alleman Apr 12 '18 at 18:01

2 Answers2

4

If you indeed have a 1-many relationship, then you add a foreign key to the first table. For instance:

create table students (
     . . . 
    projectId int not null references project(projectId)
);

The not null makes this 1-many. In most cases, you probably want 0/1-many. If so, then remove the not null constraint.

You would only introduce a third table (known as an "association table", "junction table", "cross table" and other names) if students could work on more than one project.

Note that you might want a third table if the student-project relationship can change over time. At any given time, a student works on one project, but over time, if the student can work on more than one.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

If it is one to many just add new column as foreign key (projectId) in student table

Rand alrand
  • 70
  • 1
  • 10