Posted by bob1barker on 10/01/49 11:39

we are creating a database of sales agents. Basically I have a table
of about 35,000 people, a second one with 8000 offices, and a third
table of around 400,000 transactions done by those 35,000 people in
those 8000 offices. We get new data everyday that just updates the
existing tables with the updated rosters and transactions.

I want to build a quick website where our recruiters can look up those
people and keep contact info and all that fun stuff but also pull
numbers on those people. Like

* How Many Transactions that Sales Agent did last year
* Rosters by office showing production

The goal is to click the users name and see all the percentages,
commissions and data like that which we will get by searching that
table of transactions by the agents ID.

the problems I see right away are stuff like
* If I were to pull a report showing all agents in a single office with
their number of transactions next to their name, that is a HUGE query.
It would have to search the 400k worth of records for each of the
agents on just that one report.

A suggestions I was given
I was told by a fellow programmer a better way to do this is to have an
additional table that houses stats info and have the SQL server run
automated reports everyday at say midnight where it updates that table.
This table could show stuff like
** number of transactions for each user
** avg sales price on all transactions for each user
** avg commission on transaction for each user

let me know your thoughts

thanks in advance
Monkey Girl

[Back to original message]