In a company there are 2 groups of people. One group gets paid on commission
50% for all gross revenue they bring in. Another group gets paid on
commission, but on a tiered basis. That is, someone can make:

            50% on 0 to 100,000
            55% on 100,001 to 250,000
            60% on 250,001 and above

Someone in the tiered group has been working since Jan 1 and has passed the 
150,000 mark on March 15th. The current date is April 30 and his total gross
revenues year to date are 300,500. There are other people in the group, with
different gross revenues year to date.

How would you structure the DB and calculate their total YTD commissions in
SQL?

try domyhomework.com


Show quoteHide quote

On Thu, 8 Sep 2005 07:31:15 -0700, tmyint wrote:

Show quoteHide quoteHi tmyint,

I agree with marcmc that this smells like a homework assignment - my
apologies if I've got it wrong.

Let me give you some hints, though (I don't give full solutions when I
suspect homework, but I do give the kind of hints I'd give when you were
in my class and looking puzzled at the assignment).

Before structuring the DB, give the requirements a second thought. The
information you've given is sadly lacking in completeness. For example:
how do you diferentiate between members of the "first" group and members
of the "second" group? Can people move from one group to another? If so,
on what conditions, and how is this registered? And will the commission
be based on "old" group up to the day of the move and "new" group going
forward, or will commission be changed for the complete current year (or
maybe even further back) when someone switches group?

The numbers you have given above, are they the same for anyone in the
"second" group? Or do they all have individual tables for how to
calculate the commission from the revenue? The text "someone can make"
seems to hint in this direction.

Also, can these tables change over time? If so, will the limits of the
tiers stay the same and the percentages change, or can the limits change
as well? And how about the number of tiers?
And if the tables don't change over time, then what's the relevance of
the dates mentioned in the example? Why do you state when the example
person passed the 150,000 mark (which has no special meanning, given the
sample data), but not when he passed the 100,000 or the 250,000 mark?

The table itself springs some questions as well. Is the calculation done
on total sales for each month? Or on cumulative sales? Or maybe on
cumulative sales during the current year? And when does a new year
start? You can't assume that all personnel conveniently started working
at the beginnen of the calendar year!

Oh, and what is the revenue of someone sells for 100,000.50? The table
above indicates that this por fellow gets nothing at all!!

Finally - will someone with sales of 140,000 get 55% of 140,000, or 50%
of the first 100,000 and 55% of the remaining 40,000?

Once you've gotten the answers to all the above questions (and to the
questions that will pop up while interviewing people to get those
answers), you can start structuring the DB. That's actually the easy
part - find out the functional dependencies of all the information
elements and start normalizing up to AT LEAST third normal form (but
BCVF, 4NF or even 5NF are of course preferred). Or use a modelling
technique like ORM or NIAM and map the results straight to a normalized
table design.
If you experience problems in this step, feel free to post to the group
with all the answers to the above questions, an overview of what you
have deduced from these answers and a description of the particular
problem you're stuck on.

Don't even think about writing the query to get the total YTD
commissions until you have finished the above. The query depends on the
database structure, not the other way around.
If you experience problems in this step, again feel free to post to the
group, but in this case you should provide us with the table structure
(as CREATE TABLE statements), sample data (as INSERT statements) and
expected results.

Best, Hugo

clustered index on a identity field
ROWS with null values getting truncated.
Dynamic order by case expression problem
Generating an Index
complicated update (for me)