Database modeling problem

Question

I created a system that has control of sales (id, id_vendedor, produto), salesman (id, nome, id_equipe) and team (id, nome) in a company.

Example: "John has id: 1 and belongs to the team: Blue; in January and the first 15 days of the month he sold 30 products; soon after the company moved him to the Red team and sold 25 products on the remaining days of the month. This company has a total of 20 sellers.".

To make a report table with the Seller’s Name, the products and teams you belong to is easy to make, but I would like to make a report in which it shows João’s name and shows the amount of products sold by the team and in the end shows the final amount added in the two teams and the value.

The difficulty is as follows as John has only one id (and this is unique to each seller) and I can only edit the team. How can I save in the system the id, name and team the seller is currently participating in?

Answer 1

The problem with your data model is that there is no track record of teams a seller was a part of. That is, if the seller was part of 10 teams throughout his history, sales will be accounted for only the current team.

In short, your current model is this:

Ideally, the product, the seller and, optionally, the team by which the sale was made should be informed. Thus, it is possible until a product has been sold by a seller without it necessarily being part of a team.

The model, then, would look like this:

Note that in this modeling I sought to solve only your problem in particular, but it may require greater care in a case in the "real world" - for example, quantity of products, team history for a seller etc.

I hope I’ve helped. :)

Answer 2

As it stands on the table vendas There is no way to know which team the seller was on when the sale was held, since he can change teams after making the sale. The simplest solution to this (there are others) would simply be to add a field id_equipe on the table vendas which corresponds to the team in which the seller was at the time of the sale.

Also, there is no table vendas nothing indicating when a particular sale took place, so it is not clear which ones were sold in the first 15 days of January or the like. The most obvious solution to this would be to add a field data_venda on the table vendas of the kind timestamp.

---

Consultation behind products sold by a particular seller in a given period:

SELECT v.id, v.data_venda, vd.nome AS nome_vendedor, e.nome AS nome_equipe, v.produto
FROM vendas v
INNER JOIN vendedor vd ON v.id_vendedor = vd.id
INNER JOIN equipe e ON v.id_equipe = e.id
WHERE v.id_vendedor = :id_vendedor
AND v.data_venda BETWEEN :limite_inferior AND :limite_superior

Where :id_vendedor is the id of the seller from whom one wishes to obtain the sales made and :limite_inferior and :limite_superior is the period to be considered.

---

Query showing the amount of products sold per team in a given period:

SELECT e.id, e.nome, COUNT(v.id) AS qtd, v.id_equipe
FROM vendas v
INNER JOIN equipe e ON v.id_equipe = e.id
WHERE v.data_venda BETWEEN :limite_inferior AND :limite_superior
GROUP BY v.id_equipe

Where :limite_inferior and :limite_superior is the period to be considered.

---

Query showing the amount of products sold per team and per seller in a given period:

SELECT e.id AS id_equipe, e.nome AS nome_equipe, vd.id AS id_vendedor, vd.nome AS nome_vendedor, COUNT(v.id) AS qtd, v.id_equipe, v.id_vendedor
FROM vendas v
INNER JOIN vendedor vd ON v.id_vendedor = vd.id
INNER JOIN equipe e ON v.id_equipe = e.id
WHERE v.data_venda BETWEEN :limite_inferior AND :limite_superior
GROUP BY v.id_equipe, vd.id

Where :limite_inferior and :limite_superior is the period to be considered.