Create more fields and avoid JOIN decreases load?

Asked

Viewed 68 times

1

I have 3 tables that are connected:

1ª tabela: 
GRADE (grade de horários)
Campos: ID - GRADE - ESTADO - VALIDADE 

2ª tabela: 
BLOCOS (blocos de horários da grade)
Campos: ID - ID_GRADE - BLOCO - DIA_SEMANA - DURACAO

3ª Tabela:
COMERCIAIS
resumidamente estes campos: ID, ID_CLENTE, ID_GRADE, ID_BLOCO

I’m wearing a FK to connect the Ids to their respective references in the tables.

My doubt is: In the 3rd table can be see the foreign key ID_GRADE, who is also in the 2nd and I put her thinking about decreasing the load in the database, because if by chance I try to put the GRID table in ESTADO-> desativado, the system before will search if there are any records to be executed before changing its state to disabled.

I know I could do it using JOIN in the BLOCKS table, but I think I can save the ID_GRADE, I avoid using JOIN, since the COMMERCIAL table will have many records.

This my thinking is correct or I am creating fields atoa ?

php mysql join
  • 1

    Look to read about Modeling and Normal Shapes

2 answers

1

Do not repeat the field, use JOIN, create an index in the column. Think about the relationship that exists between your entities. Then analyze the performance of the query.

  • But for example in the GRID table I have a field called ID_EMISSORA, this field is connected to the ID field in the STATION table But in the COMMERCIAL table I need to know if that particular record is for STATION [1], [2], or [3] In this case I would make a SELECT in the COMMERCIAL table to return me only the records of STATION 1, in this case I would have to give a JOIN in the BLOCKS table and consequently another JOIN in the GRIDS table. It is advisable to do this or in this case it is better to register ID_EMISSORA as well ?

1

It’s not very recommended to do this

Pros:

You win in performance in queries;

Visibly it is better to consult in the table.

Cons:

Your application will have to handle the two tables when you have change;

You lose in disk size once the table will get bigger;

--

There are others, but I think these are the ones we should look at the most.

To have no performance problem the best is to work with primary keys, do a job on top of their contents and maintenance.

In the company I work we have tables with thousands of lines and working right with the indexes we make queries with great performances.

You can also search on bank tunning, but this is another story also rs

  • in this case I would save only the ID of the reference table, for when it was for example looking for all the record of GRADE ID 1 it would return me without using JOIN and the same would not have direct modification, since it would only be allowed to delete a GRADE if it was done directly in the BANK

Browser other questions tagged php mysql join

You are not signed in. Login or sign up in order to post.