In what table it is better to put foreign key in a 1-to-1?
Let's have a table "Teams" and a table "coaches". One team may have 1 coach and 1 coach may be on 1 team.
In some of these tables it is better to create a foreign key? Ie in the table "Team" is better to make the field "instructor_id" and the FOREIGN KEY, or better the table "coaches" make the field "team_id" and FOREIGN KEY.
I guess it depends on the implementation/data structures and from which table will be the first to get the data (to make the foreign key), but perhaps there's some rule in this plan?
You need to determine which entity is the main and which is secondary for your program. How would it look? First, start a list of coaches and then these are attached to the team? Or, on the contrary, there is a list of commands and they need to create in the coach? Maybe, this is equivalent to essence, and you want to be able to get a list of commands and list of coaches independently from each other, and then put the link?
Put a foreign key to the entity that will have multiple connections with higher probability.
For example, if it is a social network for coaches, it is likely that the coach will be able to add a list of teams which he coached. And then trainer_id useful in teams.
But if your social network will grow and there will be a base of teams that coach will be able to choose from the drop down list (i.e. teams can also be a lot of coaches), you have to do M:N. Your case is a 1:1 arose only because your app is still insufficiently developed.
Also don't forget that 1 to 1 is implemented by assigning UNIQ constraint on the foreign key. Otherwise, it's 1 to many.
justina.Bahringer answered on March 19th 20 at 09:21
It's not about a single foreign key, two DIFFERENT foreign key, and You need to create both.
PS this is assuming that you have to be a coach attached to the team, and the team to the coach
Jordi_Hauck60 answered on March 19th 20 at 09:23
The situation, which will not fit into your model.
Table "Person", "Role", "Team" and "Team" (a team, person and role) will allow to avoid a situation of "player-coach" or "trainer part time".
mark89 answered on March 19th 20 at 09:25
it is obvious that in this situation foreign keys simply do not need. and they need to maintain referential integrity and it is the only rule.