Structural database, address table for two different entities

Question

I have these four tables:

Clientes | Lojas | Endereço | Contato

As much as lojas how much clientes possess address and contact phones, there is only one table for endereços and contatos, to identify each record there is a prefix for each insert: C for client and L to store, so I have records like this: tabela contatos

ContatoId | contato_dddCelular | contato_celular | contato_dddtellefone | contato_telefone
C1        |11                  | 912341234       |11                    |12341234
L1        |21                  | 912341234       |21                    |12341234

there are some views in the mysql who are showing an absurd slowness, after doing some tests I realized that the problem was just a few joins that are being done with the function Concat(), something like join contatos on Concat("C",clientes.clienteId) = contatos.ContatoId), after removing these join consultations were made almost instantaneously, my doubt is the following which would be a alternative relationship or ideal to fix this problem without having to create two address tables and contacts?

Answer 1

I particularly did not understand the need and identify by a code which address belongs to whom if the address id must necessarily be attached to the customer or store to which it belongs. For example, the id address 7 will always be referenced to the L1 store or the C40 client (arbitrary numbers to show the idea). At least that’s the interpretation I make. In my view, just correctly reference the address id and the owner’s id (customer or store) in the join table. However, one can better reflect on this if you post your tables so that one can analyze them with greater care.

Answer 2

Look buddy, I’m seeing a mistake that I consider to be the preponderant of why your code is giving problems.

Your modeling is incorrect.

Which led me to this conclusion: you’re working with text fields - mixing letter and number - in an ID column.

Databases handle numbers better, avoid using anything other than this for the ID field.

If as a consequence you still need to use the column Contactoid likewise, this implies that you are incorrectly modelling the bank.

Come on. Blza, we have customers, we have stores. Shops have address, customers have address, blza, address remains the same for the two, a table Addresses, and in the customers and in the stores we have a reference column (Foreign key) for her.

What you don’t understand is the following: a store has N customers, a customer can be "customer" of more than one store - M stores); so in the store-to-customer relationship, we have an N to M (or M to N).

What is the implication of this? Simple, when we have a relation N to M, we have something we call an associative entity, which is nothing more than another table. She has a reference ID for both the customer and the store.

From what I understand, in this modeling, this associative entity is the contact. (store customer contact).

That implies two things:

1. If the customer can give 2 different addresses to two stores, I advise to take the customer’s address column, so a store will only know his address when he becomes her contact.
2. If this does not matter for your modeling, that is, once the customer updates the address in any store, it counts for all, you leave the reference the address in the customer table.

Same thing goes for the phone. Only the store has a phone - this is on the shop table itself - and the phone number can be on both the contact table and the client table, in the same way that I just explained.

The phone does not need to have an extra table, even if you put there also mobile. It is too much work for little return.

The address you may not even create an extra table, it will save you many joins, nowadays it doesn’t even have much importance vc have a 100% normalized model, maybe if you do meaningful analysis with address make some difference, but again it is too much work for little return.