Database modeling problem

Question

Database modeling problem

Navigation

#1 by (5 votes)
#2 by (5 votes)

3

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

Example: "João has id: 1 and belongs to the team: Azul; in January and the first 15 days of the month he sold 30 products; soon afterwards the company changed him to the Red team and sold 25 products on the remaining days of the month. In this company there are a total of 20 sellers. "

To make a report table with the Name of the seller, the products and teams it belongs to is easy to do, but I would like to make a report that shows the name of John and shows the amount of products sold by team and in the end show the final amount added on both teams and the value.

The difficulty is the following as John has only a id (and this is unique for each seller) and I can only edit the team. How can I save the id, name, and team on the system to which the vendor is currently participating?

sql modelagem modelo-relacional

asked by anonymous 15.01.2015 / 03:51

2 answers

Call function js in another file - Dependency between Javascript scripts How to calculate the average of a column in R?

score 5 · Answer 1

The problem with your data model is that there is no team history in which a vendor was part of it. That is, if the seller was part of 10 teams throughout his history, sales will be counted taking into account only the current team.

In short, your current template is this:

Ideally,theproduct,theseller,and,optionally,theteamforwhichthesalewasmadeshouldbeinformedofthesale.Thus,itispossibleuntilaproducthasbeensoldbyasellerwithoutitnecessarilybeingpartofateam.

Themodel,then,wouldlooklikethis:

It is worth noting that in this modeling I have tried to solve only your problem in particular, but it may be that it requires greater care in a case in the "real world" - for example, quantity of products, history of teams for a seller, etc.

I hope I have helped. :)

score 5 · Answer 2

As it is, in the vendas table, you can not tell which team the seller was when the sale was made, since he can change teams after making the sale. The simplest solution to this (there are others) would simply be to add a id_equipe field in the vendas table that corresponds to the team on which the seller was when the sale was made.

In addition, there is nothing in the vendas table that indicates when a particular sale was made, so you can not tell which were sold in the first 15 days of January or something like that. The most obvious solution to this would be to add a data_venda field to the vendas table of type timestamp .

Query that brings the products sold by a particular vendor in a certain 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 salesperson from whom sales are to be made and :limite_inferior and :limite_superior is the period to be considered.

Query that shows 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 that shows the amount of products sold per team and per vendor 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.