Where should I put FK?

Question

I’m learning about the database when a question came up.

For example, I have two tables:

user (_iduser, nome, sexo, ect.._)

login (_idlogin, login, senha_))

Have relationship 1:1. My question is precisely whether I can put FK in either of the two tables, or whether there is a rule as to how much I should follow.

Answer 1

First of all, in 1:1 relationships I put everything on one table.

Anyway if you want to separate the tables, here’s a solution:

table user

iduser (PK)
nome
sexo

table login

iduser (PF) 
login
senha

Note: here the field iduser is a Primary/Foreign Key, that is, it is both a primary key and a foreign key

One of the reasons I don’t like having two tables in 1:1 relationships is the fact that there is a need to do an INSERT first in the table usuario, draw out the iduser that was generated and only then can I do the INSERT in the table login.

Answer 2

The Code would be more or less like this:

Create Table Tb_Usuario
(
Id_Usuario Int Not Null Identity Primary Key,
Nome varchar(20) Not Null Unique,
Sexo bit Not Null
)

Create Table Tb_Login
(
Id_Login Int Not Null Identity Primary Key,
Id_Usuario Int Not Null References Tb_Usuario(Id_Usuario),
Pass varchar(10) Not Null
)

I’m not sure what you want more than this is a basic

Answer 3

I agree with what Sandro said, for this particular situation of yours. It is interesting that you always analyze the scenario you are developing. For example, in your case it is interesting to make the 1:1 relationship in the same table, because it is little information, but let’s imagine a different scenario:

You are developing for example a vehicle rental application, so you have a Rent table for example:

(Tabela - Aluguel)
id(PK)
idCarro(FK) ManyToOne
idMotorista(FK) ManyToOne
dataDevolucao
dataEntrega
dataPedido
valorTotal

And for every Rent you need an Insurance Policy:

(Tabela - ApoliceSeguro)
id(PK)
protecaoCausasNaturais
protecaoRoubo
protecaoTerceiro
valorFranquia

You noticed how the scenario changed, if you put the 1:1 relationship on the same table, your table will get a lot of information that isn’t hers. The insurance, it belongs to the rent, but it has the insurance information, so it’s a separation of responsibilities.

So for this specific scenario in the Rent table, you will get a FK as follows:

idApoliceSeguro(FK) OneToOne

About the need to perform more than one INSERT. Some framework can assist you in this task, such as Hibernate. Let’s imagine that you try to perform only (1) INSERT. The error will occur saying that the object is referencing an unsaved instance, save first this unsaved instance before downloading the data into the database. The instance he’s referring to is Apoliceseguro.

If you are using Hibernate for example, you could create a DAO from the serguro apolice, save it before, so it becomes a persisted object and managed by Entitymanager and it will have the code to be able to save the Rent.

Or, in the @Onetoone annotation, you use the property (Cascade=Cascadetype.ALL) for all operations or only for certain operations such as PERSIST, MERGE, etc.

But in your specific case, I have a suggestion, separate the responsibilities and use a relationship (Manytomany), I would do as follows:

(TABELA - usuario)
id(PK)
nome
senha

(TABELA - grupo)
id(FK)
descricao
nome

(TABELA - usuario_grupo)
id_usuario(FK)
id_grupo(FK)

That way I can manage as follows:

• GROUP = Administrator, Manager, Employee, ...
• User = user (X) belongs to the Administrator group
• User = user (y) belongs to the Manager group

And so on and so forth...

This way you manage in your application the rights that each group has and which users will receive these rights.