Database Design Question

[Joe Williams]

I have an application that needs to track commissions. Each salesperson can
sell different products that each have a certain bonus amount assigned to
them.

OK, that part is no problem. The caveat is that if the salespeople sell
ceratin amounts in a given time frame (monthly), then the commission amount
for each product goes up. For instance, if they sell 0-10 products the
commission amount is $100. If they sell 11 - 20, the commission amount is
200, and so on and so on.

I can do a simple structure whereby a ceratin product gets paid a certain
amount, but I do not know how to handle the sliding scale as more and more
sales get entered for that salesperson.

Can anyone point me in the right direction to solve this problem?

Thanks

Joe

 

[Armen Stein]

I have an application that needs to track commissions. Each salesperson can
sell different products that each have a certain bonus amount assigned to
them.

OK, that part is no problem. The caveat is that if the salespeople sell
ceratin amounts in a given time frame (monthly), then the commission amount
for each product goes up. For instance, if they sell 0-10 products the
commission amount is $100. If they sell 11 - 20, the commission amount is
200, and so on and so on.

I can do a simple structure whereby a ceratin product gets paid a certain
amount, but I do not know how to handle the sliding scale as more and more
sales get entered for that salesperson.

Can anyone point me in the right direction to solve this problem?

Thanks

Joe

Hi Joe,

This is a pretty tricky design question. It would take more discussion
than can be easily covered here, but there are some questions I would
start with:

Are the commission amounts per product ($100, $200) different by
salesperson? By product? Do they change over time?

Are the commission quantity breaks (1-10, 11-20) different by
salesperson? By product? Do they change over time?

Are there commission agreements that apply to groups of salespeople? Do
they change over time?

Are commission amounts always dollars, or are they ever percentages
instead?

How are returns and canceled sales handled? Are they deducted from the
commissions? What if a commission has already been paid, but the
product is returned later?

We wrote a complex commission system like this one a few years back, and
it worked well, but it took a lot of time and coding to construct.
You're welcome to contact me offline to discuss the specific details.

Regards,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com