Where should I place FK?

4

I am learning about database when a doubt arose.

For example, I have two tables:

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

login (_idlogin, login, senha_))

You have 1:1 relationship. My question is precisely whether I can put FK on any of the two tables, or if there is a rule as to what I should follow.

    
asked by anonymous 06.08.2015 / 09:01

3 answers

2

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

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

table user

iduser (PK)
nome
sexo

login table

iduser (PF) 
login
senha

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

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

    
06.08.2015 / 09:59
2

The code would look 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 do not know correctly what you want, this is a basic

    
06.08.2015 / 14:39
0

I agree with what Sandro said, for his particular situation. It is interesting that you always analyze the scenario that you are developing. For example, in your case it is interesting to make the 1: 1 relationship in the same table because there 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 each Rent you need a Insurance Apolice:

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

You have noticed how the scenario has changed, if you put the 1: 1 table in the same table, your table will get a lot of information that is not hers. The insurance, belongs to rent, but has insurance information, so it is a separation of responsibilities.

So for this particular scenario in the Rent table, you will have an FK as follows:

idApoliceSeguro(FK) OneToOne

About the need to perform more than one INSERT. Some frameworks can help you with this task, like 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, first save this unsaved instance before downloading the data in the database. The instance he is referring to is ApoliceSeguro.

If you are using Hibernate for example, you could create a DAO from the seraglio apolice, save it before, so it becomes an object persisted and managed by EntityManager and it will have the code to be able to save the rent. / p>

Or, in the @OneToOne annotation, you use the property (cascade = CascadeType.ALL) for all operations or only for certain operations like PERSIST, MERGE and etc.

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

(TABELA - usuario)
id(PK)
nome
senha

(TABELA - grupo)
id(FK)
descricao
nome

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

This 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 ...

In this way you manage in your application the rights that each group has and which users will receive those rights.

    
06.08.2015 / 15:30