@MajP has mentioned several times that I see this as a trivial problem. In terms of the basic organisation as described
there are just 3 levels so a simple 3 level query with each level joined to sales will do the job. But this assumes the world is perfect and does not change.
I pointed out that the OP had not considered what happens when someone leaves or changes roles within the organisation and urged them to clarify all the business rules. On the other forum this was not really answered. Perhaps it has here.
in my experience managing those changes within the application is where the complexity arises - and clear business rules need to be determined.
my experience with commissions is with large organisations with a variety of reward plans fixed for a period and with occasional strategic short term plans to meet an unforeseen threat or opportunity. Not to mention organisational change. Typical annual spend on rewards would be £20-£30m with around 1000 payees, reward typically being around 50% of total earnings.
Over the last 20 odd years I have saved my clients around £250m annually through clarifying the rules and applying them correctly. I should point out this figure includes payments to external agencies as well as employees. External agencies get paid a far higher percentage of the sale.
As far as this thread is concerned with a quick scan through it seems the rules are still unclear and trying to tackle changes and new requirements on the fly as and when the thought occurs is not the way to go. OP should sit down and consider and document all eventualities and requirements, including reporting before starting on the table/relationship design.
I haven’t read the whole if this thread so perhaps the following has been resolved
The original concept of a code ‘ABA’, ‘ABB’ etc is on the face of it a simple solution, but fraught with issues, typo’s being one. But since these are allocated to an individual they cannot be reused, so there is a limit of 26 individuals per level/group. So make it a role identifier rather than an individual. Then you have the issue of when someone starts to occupy that role, or moves on from it. Or for a short period perhaps two people share the role
is there a training period? Does that impact payments?
What happens when someone leaves? Do they continue to be paid for a period of time?
If someone is paid incorrectly - either too much or too little - how do you resolve that? Make a separate payment? Correct the following month? How does that impact on the historical record (e.g.for reporting)
these are just some of the questions I would be asking.belonged to agent
!
