In what table it is better to put foreign key in a 1-to-1?

Hello.
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?
March 19th 20 at 09:17
4 answers
March 19th 20 at 09:19
Solution
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.
And unless foreign keys can be put then? In SQLite does not see the possibility to do this... - lucio71 commented on March 19th 20 at 09:22
@lucio71, eat, this is set at the level of field types when creating a table.
trainer_id INT NULL DEFAULT NULL,
or just trainer_id INT, only then need to pass null explicitly: insert into teams (id, trainer_id) VALUES (1, null) - Jackson commented on March 19th 20 at 09:25
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
you give advice which may cause deadlock when the command cannot be deleted until you removed the coach and the coach cannot be deleted until the deleted team. Typically use one foreign key constraint, and UNIQ - Jackson commented on March 19th 20 at 09:24
And in this case add data if we have the tables are empty?
Let's say I add the first command, and must specify the id of the trainer, and coaches yet, what to do? - lucio71 commented on March 19th 20 at 09:27
@lucio71, well, you can make the foreign key nullable, i.e. the team not necessarily have a trainer \ coach not necessarily a team. Otherwise, if you do two keys - Jackson commented on March 19th 20 at 09:30
@Jackson, I'm sorry, I was referring to that field with the id of entities need to shoot in both tables, subject to the compulsory binding of both entities to each other, not setting foreign key on database level, but You're right, I'm sure the author was referring to a foreign key in the truest sense of the word - justina.Bahringer commented on March 19th 20 at 09:33
@lucio71, not to be, the DBMS will not allow you to create such a structure - mark89 commented on March 19th 20 at 09:36
@mark89, how can you be so sure? - Jackson commented on March 19th 20 at 09:39
DNkpAY0.png
create table test_table_1
(
 id serial not null
 constraint test_table_1_pkey
 primary key,
 t_2 integer
 constraint "idx-t2"
 references test_table_2
 on delete cascade
);

create index "idx-t2" on test_table_1 (t_2);

create table test_table_2
(
 id serial not null
 constraint test_table_2_pkey
 primary key,
 t_1 integer
 constraint "idx-t1"
 references test_table_1
 on delete cascade
);
create index "idx-t1"
 on test_table_2 (t_1);
- Jackson commented on March 19th 20 at 09:42
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".
Um... what? - lucio71 commented on March 19th 20 at 09:26
@lucio71, you proposed the implementation of the communication "many-to-many", i.e. the most common case. I wish I realized because in the case of teams and coaches is a high probability that there will be a situation of "a team has several coaches," etc.
If you 150% sure that this will never happen, the table "Composition" is not needed. - clara_Connel commented on March 19th 20 at 09:29
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.
Why? O_0 - lucio71 commented on March 19th 20 at 09:28
@lucio71, because the team may be without a coach and a coach without a team - mark89 commented on March 19th 20 at 09:31
@mark89, then you can just allow the field to write is NULL and all, doesn't that solve the problem? - lucio71 commented on March 19th 20 at 09:34
it is obvious that in this situation foreign keys simply do not need

to whom is it obvious? you're contradicting yourself. The key is always needed when you need integrity. Do you need integrity to be addressed by the developer. If he wants integrity and the keys are needed. Do not enter people astray. - Jackson commented on March 19th 20 at 09:37

Find more questions by tags Database design