By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Table design and relationships for a Tiered Commission Database

P: n/a
Hello

I am trying to create a database to calculate commissions on a sale
based on a tiered commission schedule and am having trouble with how
to design the tables and relationships to store the info needed.

Each sale will have 1 or 2 sales reps which are assigned to a tier.
For example:
repA is assigned to Tier 1
repB is assigned to Tier 2
repC is assigned to Tier 3

If repB & repC completes a sale then the following commissions are
paid out
repA receives 4% of sale amount
repB receives 8% of sale amount
repC receives 4% of sale amount

If repA completes a sale then:
repA receives 10% of sale amount

What fields should i include in the table to help determine the
commissions paid out. We may add more Tiers in the future so I would
like to design the structure to be easliy scalable.

Thank you in advance for your time.
Matt
Oct 10 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Oct 10, 11:22*am, spima05 <mspilot...@gmail.comwrote:
Hello

I am trying to create a database to calculate commissions on a sale
based on a tiered commission schedule and am having trouble with how
to design the tables and relationships to store the info needed.

Each sale will have 1 or 2 sales reps which are assigned to a tier.
For example:
repA is assigned to Tier 1
repB is assigned to Tier 2
repC is assigned to Tier 3

If repB & repC completes a sale then the following commissions are
paid out
repA receives 4% of sale amount
repB receives 8% of sale amount
repC receives 4% of sale amount

If repA completes a sale then:
repA receives 10% of sale amount

What fields should i include in the table to help determine the
commissions paid out. We may add more Tiers in the future so I would
like to design the structure to be easliy scalable.

Thank you in advance for your time.
Matt
tblRep
repCode
repName

tblOrder
salesOrder
tblOrderRep
salesOrder
repCode
tier
commissionRate

I save the commissionRate in case the business rules change
tomorrow

and then you have a form to calculate the commission, and it applies
the 4, 8, 4, 10% rules
Oct 10 '08 #2

P: n/a
On Oct 10, 6:51*pm, Roger <lesperan...@natpro.comwrote:
On Oct 10, 11:22*am, spima05 <mspilot...@gmail.comwrote:


Hello
I am trying to create a database to calculate commissions on a sale
based on a tiered commission schedule and am having trouble with how
to design the tables and relationships to store the info needed.
Each sale will have 1 or 2 sales reps which are assigned to a tier.
For example:
repA is assigned to Tier 1
repB is assigned to Tier 2
repC is assigned to Tier 3
If repB & repC completes a sale then the following commissions are
paid out
repA receives 4% of sale amount
repB receives 8% of sale amount
repC receives 4% of sale amount
If repA completes a sale then:
repA receives 10% of sale amount
What fields should i include in the table to help determine the
commissions paid out. We may add more Tiers in the future so I would
like to design the structure to be easliy scalable.
Thank you in advance for your time.
Matt

tblRep
* *repCode
* *repName

tblOrder
* *salesOrder

tblOrderRep
* *salesOrder
* *repCode
* *tier
* *commissionRate

* * * I save the commissionRate in case the business rules change
tomorrow

and then you have a form to calculate the commission, and it applies
the 4, 8, 4, 10% rules- Hide quoted text -

- Show quoted text -
Thank you for your response Roger. I am a little confused with the
tblOrderRep, should i include a field for the 2nd rep that might be
part of the sale?

Thank you in advance.

And I would appreciate suggestions from anyone else who may ideas.

Thank you.
Matt
Oct 11 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.