Should I avoid Queries?

Question

Example scenario

I have 3 tables: relatorios, atividades and carros.

Table relatorios, will be the "cover" of the day, with date, initial and final mileage, etc.

Table atividades, will be each activity held on the day, linked to a record relatorio.

Table carros, with registration of available vehicles.

Let’s consider that there are 100 employees, with approximately 10 activities per day, generating 100 reports and 1000 activities.

The car will be changed every 6 months between employees, so they will stay 3 months with the same vehicles.

---

Example 1

I can have a field carroid on my table relatórios, so when generating an analytical query, make a join and I already know the used car.

SELECT * FROM atividades a
LEFT JOIN carros c ON c.id = a.carroid

---

Example 2

As it does not change much, which car is being used, could create a 4 table.

Table carroperiodo, where I have only the start date of use (datainicio).

So I would use subquery to query the date, which car was used:

SELECT *,
(SELECT carroid FROM carros WHERE datainicio <= r.data LIMIT 1)
FROM atividades a
LEFT JOIN relatorios r ON r.id = a.relatorio

---

Doubts

• When it’s worth it I get one over and over again id in a table, to avoid a subquery?
• How much Queries affect the performance of a query?
• How could I improve them? Or not use them at all!?