Relationship on-to-one in auxiliary table using Laravel

Question

In the application I am working the database is built on the part of users, as follows:

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

In the user table there is no column referencing the address. What exists is an auxiliary table (which we normally use for n to n relations) with user_id and address_id in a ratio of 1 to 1 from both user to user_address and user_address to address. The argument of the staff of the below is that this would avoid the existence of a direct address_id field in the user table and this was blank (because the address is not mandatory).

At the beginning of 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, so in the User model I would only 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 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. All I have left is two questions:

1. The way the database is built is correct?
2. What is the most appropriate way/method to retrieve the user address directly?

Answer 1

I wouldn’t misjudge the way your database was modeled. In fact, your 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.

The project suggests 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 world of technology it is not usually very useful to have an option to link more than one user to the same address. If your project is able to reach this point, keep this structure at any cost.

The project suggests that the same user has more than one address?

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

The project suggests that a user can never have more than one address?

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

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

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

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

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

---

A personal addition I suggest is that you never limit trivial things when there is no strict reason for it. It is much better if you return a list of addresses and ensure that Frontend travels even if only by one address than creating a limitation that in the future may require reworking to undo the previously created limitation. Thus, if the frontend travels 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 for this. Just rework the styling/appearance.

Answer 2

Initial consideration

Programming must adapt to the bank, not the bank to 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.

Bench

• When I ride a bench I having you think about the possibilities and almost always avoid using relationship 1:1 <=> 1:1, as you have, the ideal would be 1:1, because it avoids having extra tables as well as JOIN, CONTRAINS, INDEX, etc..
  After all, what’s the point of making one INDEX in user if user_adresss all mixed up?
• Separate the subject is interesting, but the exaggeration is never beneficial, not the problem at all a have a column with NULL at the bank.

Relationship

Assuming the following information :

• Person {name, Cpf, age}
• User {login, password, admin::Boolean}
• Address {address, neighborhood, in a}
• Position {profession, salary, salary}

Types of relationship :

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

Which one do you think is cleaner?

Programming

In programming it would be the same if, check if you have in the auxiliary table or check if this NULL.
Ja no SELECT mute :

• 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