milkman2500
Registered User.
- Local time
- Today, 09:49
- Joined
- Oct 21, 2012
- Messages
- 45
Hi,
I'm currently trying to model out the relationships for a sales database. I've walked through the structure with the client and established the relationships. However, I'm concerned about future exceptions and how to position the database to accommodate.
The current model is as follows:
An employee earns a specific rate in a specific role:
John will earn 10% as a sales executive, and 5% as a sales management.So if John sold Client ABC, he will earn 10%. However, if John assisted Adam in selling Client ABC, Adam will earn 10% and John will earn 5% on what Adam earns.
Alternative 1
I've modeled the tables as t_employees, t_clients, t_roles. The rate is within t_roles. So if John is assigned as a sales executive, he will always receive 10%. However, if in the future they want to assign John a 15% rate on a client as an exception, the user would have to create a new role as "Exception 1" and populate the table with the 15% rate, then assign that role to John.
Alternative 2
The alternative, is to create a relationship table (t_sales_lkp), where the employee, client, role and rate will need to be defined every time a new client comes on board. This creates redundancy as the user will need to constantly reapply the same rate over and over again.
Suggestions?
I'm currently trying to model out the relationships for a sales database. I've walked through the structure with the client and established the relationships. However, I'm concerned about future exceptions and how to position the database to accommodate.
The current model is as follows:
An employee earns a specific rate in a specific role:
John will earn 10% as a sales executive, and 5% as a sales management.So if John sold Client ABC, he will earn 10%. However, if John assisted Adam in selling Client ABC, Adam will earn 10% and John will earn 5% on what Adam earns.
Alternative 1
I've modeled the tables as t_employees, t_clients, t_roles. The rate is within t_roles. So if John is assigned as a sales executive, he will always receive 10%. However, if in the future they want to assign John a 15% rate on a client as an exception, the user would have to create a new role as "Exception 1" and populate the table with the 15% rate, then assign that role to John.
Alternative 2
The alternative, is to create a relationship table (t_sales_lkp), where the employee, client, role and rate will need to be defined every time a new client comes on board. This creates redundancy as the user will need to constantly reapply the same rate over and over again.
Suggestions?
