We are trying to add a couple of new tables to an existing database to help us track sales commission related data. The database already has three pre-existing tables, PJEMPLOY, PJPROJEM, and PJPROJ (please see the attached diagram). All employees (account managers/recruiters, consultants, etc.) are listed in the PJEMPLOY table and all company projects are listed in the PJPROJ table.

We need to be able to track which employees are either account managers or recruiters, which consultants they earn a commission off of, and what percentage of each specific consultant's salary (it varies per consultant) is earned as a commission for each account manager/recruiter.

What is the best way to structure these tables for querying/reporting purposes? Any new data will be manually entered, however, we would like to easily be able to at least be able to generate queried rows of the following data: account manager/recruiter name (already contained in PJEMPLOY), Consultant Name (already in PJEMPLOY table, we just need a means of determining who is an account manager who is a consultant), Employee Sales Plan (this will be the % the account manager will earn, currently not stored in any table), and Employee Salary (this is the consultant's salary, currently not stored anywhere).

Any insights would be appreciated!
Commission-Tracking-Table-Layout.jpg