Table design and relationships for a Tiered Commission Database

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • spima05
    #1

    Table design and relationships for a Tiered Commission Database

    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
    Tags: None
    • Roger
      #2
      Re: Table design and relationships for a Tiered Commission Database

      On Oct 10, 11:22 am, spima05 <mspilot...@gma il.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

        Comment

        • spima05
          #3
          Re: Table design and relationships for a Tiered Commission Database

          On Oct 10, 6:51 pm, Roger <lesperan...@na tpro.comwrote:
          On Oct 10, 11:22 am, spima05 <mspilot...@gma il.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

            Comment

            Previous template Next
            Working...