Stable database, address table for two different entities

Question

Stable database, address table for two different entities

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have these four tables:

Clientes | Lojas | Endereço | Contato

As both lojas and clientes have address and contact telephones , there is only a single table for endereços and contatos , to identify each register there is a prefix for each insert: C for client and L for 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 mysql that are showing an absurd slowness, after doing some tests I realized that the problem was just some joins being done with the Concat() function, something like join contatos on Concat("C",clientes.clienteId) = contatos.ContatoId) after removing these join queries were done almost instantly, my question is what would be an alternative relationship or ideal to fix this problem without having to create two address and contact tables?

asked by anonymous 19.11.2018 / 21:21

2 answers

0

I have not particularly understood the need and identify by a code which address belongs to whom the id of the address should necessarily be tied to the customer or store to which it belongs. For example, the address of id 7 will always be referenced to store L1 or to client C40 (arbitrary numbers to show the idea). At least that's the interpretation I make. In my opinion, it is enough to correctly reference the id of the address and the id of the detaining entity (client or store) in the join table. However, one can better reflect on this if you post your tables so that you can analyze them with greater care.

19.11.2018 / 21:41

Use Spreadsheet API with ESP8266 or AJAX How to convert variable from type number to string in typescript?

score 0 · Accepted Answer

Look, friend, I'm seeing a bug that I consider to be the preponderant reason your code is giving trouble.

Your modeling is incorrect.

What led me to this conclusion: you are working with text fields - blending letter and number - into an ID column.

Databases best deal with numbers, avoid using anything other than that for the ID field.

If, as a consequence, you still need to use the ContactId column in the same way, this implies that you are incorrectly modeling the database.

Come on. Blza, we have Customers, we have Stores. Stores have address, clients have address, blza, address remains the same for both, a table Addresses, and in customers and stores we have a reference column ( foreign key ) for it. >

What you do not understand is the following: a store has N customers, a customer can be "customer" from more than one store - M stores); then in the store-to-customer relation, we have a relation N for M (or M for N).

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

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

This implies two things:

If the customer can give 2 different addresses to two stores, I advise you to take the customer's address column, so a store will only know about his address when he becomes a contact.

If this does not matter for your modeling, that is, once the client updates the address in any store, it is worth all, you leave the reference to the address in the client table.

The same goes for the phone. But the store has a phone - this is in the store table itself - and the phone number can be in both the contact table and the customer table, just as I just explained.

The phone does not need to have an extra table, even if you also have a cell phone. It's too much work for little return.

The address you can not even create an extra table, this will save you many joins, nowadays nor do you have much importance you have a 100% normalized model, maybe if you do significant analyzes with address make a difference, but again it's a lot of work for little return.