On-to-one relationship in auxiliary table using Laravel

Question

On-to-one relationship in auxiliary table using Laravel

Navigation

#1 by (3 votes)
#2 by (2 votes)

5

In the application I'm working the database is built on the users part, as follows:

user 1 -------> 1 user_address 1 <-------- 1 address

In the user table there is no column that references the address. What exists is an auxiliary table (which we normally use for n-to-n relations) with the user_id and the address_id in a 1-to-1 relationship both from user to user_address and from user_address to address. The staff's argument is that this would avoid a direct address_id field in the user table and it would be blank (since the address is not required).

When I started the models, I came across the following situation: If, only if , the user table had a direct relationship with the address table from one to one, then in the User model I only would need to put:

public function address(){
    return $this->hasOne('App\Address');
}

However it occurred to me that if I do this with the user_adress model I will not have the address directly but only the address_id.

Using hasManyThrough I could get the address this way (following the documentation):

public function addresses(){
    return $this->hasManyThrough('App\UserAddress', 'App\Address');
}

However this method returns a list, and in my case, I would only always have at most one record. I have two questions:

Is the database the correct way?

What is the most appropriate way / method to retrieve the user's address directly?

php modelo-relacional laravel laravel-eloquent

asked by anonymous 05.01.2016 / 07:33

2 answers

2

Initial Consideration

Programming must adapt to the bank, not the bank programming, certainly the bank is more constant than programming, because it involves analysis, and is usually 'modeled' by the DBA , already the code any of your colleagues can change.

Bank

When I build a database I think about the possibilities and almost always avoid using 1:1 <=> 1:1 relationship, as you have, the ideal would be 1:1 , because it avoids having extra tables like JOIN , CONTRAINS , INDEX , etc.
After all, what's the point of doing INDEX in user if user_adresss is all mixed?
Separating the subject is interesting, but overkill is never beneficial, not a problem to have a column with NULL in the database.

Relationship

Assuming the following information:

Person {name, cpf, age}
User {login, password, admin :: boolean}
Endereco {address, neighborhood, num}
Charge {profession, salary, payload}

Relationship types:

1:1 <=> 1:1
1:1

What do you think is cleaner?

Programming

In programming a would be the same as if , check if you have in the helper table, or check if this NULL . Ja no SELECT changes:

1:1 <=> 1:1

SELECT
    *
FROM
    pessoa p
    INNER JOIN pessoa_endereco pe ON pe.cd_pessoa = p.cd_pessoa
    INNER JOIN endereco e ON pe.cd_endereco = e.cd_endereco

1:1

SELECT
    *
FROM
    pessoa p
    INNER JOIN endereco e ON p.cd_endereco = e.cd_endereco

05.01.2016 / 15:17

How does php guarantee a single session_id? Use multiple database

score 3 · Accepted Answer

I would not misjudge the way your database was modeled. In fact, its structure is already prepared to accept more than one address per user and / or link an existing address to a different user. However, you would need to think better about the business rather than just the code / programming.

Does the project suggest the possibility of registering more than one user at the same address?

Although this is a reality (people usually share the same address with parents, spouses, republics, etc.), in the technology world it is often not very useful if you have an option to link more than one user to the same address . If your project is able to reach that point, keep this structure at any cost.

Does the project suggest that the same user has more than one address?

It is common for projects that require the possibility of multiple addresses. A very simple example is eCommerce. You have a) the billing address and b) the delivery address. For a second purchase, you may not want the same previous address, but you also do not want to remove it from the system for future purchases to be made easier. Excluding the previous possibility, this model would adopt a relation 1 - > N (1 user, N addresses) where the structure would be best if you removed the middle table and kept only the address table. In it, you would have a user_id that would solve your problem.

Does the project suggest that a user can never have more than one address?

In this case you can choose to a) keep the address data inside the user table or b) create a supplementary table.

When it comes to supplementary table, things can get a little complicated depending on how you decide to work. You can, for example, relate the id column of the user table directly with the id column of the address table. That way, if a user has registered an address, his address will be in the same id that he represents. If the user has not registered, the numbering of his id will be empty in the address table. Another common way would be to keep the same pattern as the previous question (1 -> N), but limit the code to only one address.

One way to achieve this is by specifying in the relation itself:

public function address(){
    return $this->hasMany('App\Address')->first();
}

The first() method in the relationship will ensure that you always load only one record. Just ensure that at the time of insertion, there is no record for the specified user.

A personal compliment that I suggest is that you never limit trivial things when there is no strict reason for doing so. It is much better that you return a list of addresses and ensure that Frontend traverses even one address than creating a limitation that in the future might require rework to undo the previously created limitation. Thus, if the frontend goes through the array with only 1 record and in the future the project decides to accept more than 1 address per user, its code is prepared to do so. Just rework the styling / appearance.