In which table is it better to put a foreign key in a 1 to 1 relationship?

Question

Database design

Hello.
Let's say there is a "Teams" table and a "Coaches" table. One team can have 1 coach and 1 coach can have 1 team.
In which of these tables is it better to create a foreign key? Those. in the "Teams" table it is better to make the "instructor_id" field and FOREIGN KEY or it is better to make the "team_id" field and FOREIGN KEY in the "Coaches" table.
I guess it depends on the implementation / data structure and on which table the data will first fall into (to then make a foreign key on them), but maybe there is some kind of rule in this regard?

Answer 1

You need to determine which entity is primary and which is secondary to your program. How will it look like? First, a list of coaches is started and then teams are attached to them? Or, on the contrary, there is a list of teams and you need to create a coach for them? Or maybe they are equivalent entities, and you want to be able to create a list of teams and a list of coaches independently of each other, and only then make connections?
Put the foreign key of the entity that will not have a multiple relationship with a higher probability .
For example, if this is a social network for coaches, then most likely the coach will be able to add a list of teams that he coached. And then trainer_id will come in handy in teams.
But if your social network grows and there is a base of teams that the coach can select from the drop-down list (i.e. teams can also have many coaches), then you will have to do M:N. Your case of 1:1 communication only arose because your application hasn't evolved enough yet.
Also remember that 1 to 1 is implemented by assigning a UNIQ constraint to a foreign key. Otherwise it's 1 to many.

Answer 2

This is not about one foreign key, these are two DIFFERENT foreign keys, and you need to create both.
PS this is provided that you must have a coach attached to the team, and the team to the coach

Answer 3

There will be a situation that does not fit into your model.
The tables "Person", "Role", "Team" and "Composition" (team, person and its role) will allow you to avoid the situation of "playing coach" or "part-time coach".