For a telemarketing company I need to make a functionality where the telemarketing manager (by clicking on a name of a marketeer) can see the cumulative sum (per day, week or month) of sales made by that marketeer and I am realy struggling what the structure of the tables should be? Obviously I have a table marketeers, i.e. ( marketeer_id, name, password, etc) but what should be the structure of the other tables involved that is holding the sales made by that marketeer and where the dates are set. Because the final sales after a month should also be stored so he/she (the manager) will have an overview per mont as well. I hope the question is clear enough?
you obviously need a table to hold the individual sales, using sales amount plus marketer id and date
then a summary table with sales amount totals plus marketer id and year-and-month
then perhaps another summary table with sales amount totals plus marketer id and year
to show current month sales, query the daily table
to show current year sales, query daily table UNION monthly table
to show sales to date, UNION all three
Hi Rudy. Thank you for the reply. Let me see if I understand you right. I have created the following tables:
CREATE TABLE IF NOT EXISTS `marketeers` (
`marketeer_id` smallint(3) NOT NULL AUTO_INCREMENT,
`v_naam` varchar(128) DEFAULT NULL,
`a_naam` varchar(128) NOT NULL,
`username` char(7) NOT NULL,
`salt` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`isActive` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`operator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `marketeer_bericht_aantallen` (
`aantal_id` bigint(20) NOT NULL AUTO_INCREMENT,
`marketeer_id` smallint(3) NOT NULL,
`aantal` smallint(6) NOT NULL,
`datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`aantal_id`),
KEY `marketeer_id` (`marketeer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `marketeer_bericht_totalen` (
`totaal_id` bigint(20) NOT NULL AUTO_INCREMENT,
`marketeer_id` smallint(3) NOT NULL,
`totaal` smallint(6) NOT NULL,
`maand` date NOT NULL,
`jaar` date NOT NULL,
PRIMARY KEY (`totaal_id`),
KEY `marketeer_id` (`marketeer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The second tables is indeed, as you said, obvious. When the marketeer makes a sale the following information wil go in that table:
marketeer_id, 1, CURRENT_TIMESTAMP
after the insert I check id if the marketeer is already present in the third table (marketeer_bericht_totalen). If he is not I nsert the values in the table, i.e.
marketeer_id, 1, month, year
If on the other hand the marketeer is present in the table I should check if the month in the excisting row is the present month and the year is the present year. If that is the case I update that row, i.e.
UPDATE marketeer_bericht_totalen SET totaal = totaal + aantal
I do hope I see it the right way. If not, I hope you can explain me where I am going wrong.
Thank you in advance
that will work
however, i would not add to the monthly table until the month is over, using this type of query –
INSERT
INTO marketeer_bericht_totalen
( marketeer_id
, totaal
, maand
, jaar )
SELECT marketeer_id
, SUM(aantal)
, 06
, 2014
FROM marketeer_bericht_aantallen
WHERE datum >= '2014-06-01'
AND datum < '2014-07-01'
GROUP
BY marketeer_id
two comments –
first, the hardcoded values 06, 2014, ‘2014-06-01’, and ‘2014-07-01’ represent the month/year that is being summarized and will change every month, being inserted in the query by your application language (php or whatever)
second, the maand and jaar columns need to be TINYINT and SMALLINT, not DATE
Hi Rudy. Again thank you for the reply and tips (second, the maand and jaar columns need to be TINYINT and SMALLINT, not DATE ) Why I need the insert into marketeer_bericht_totalen like that is because the company would like to have the marketeers can see their own sales stats as well. Next to a basic salary, they are getting commisions bassed on the number of sales, so that could be a stimulance.
well, like i said in my first post…
to show current month sales, query the daily table
to show current year sales, query daily table UNION monthly table