Where to Put Instructor Salary?

[alpinegroove]

I am building a database with information about courses and instructional staff in my school. Each instructor can teach more than one course and have different roles in each course (Instructor, Teaching Assistant, Reader), so I have a junction table to address the many-to-many relationship and assign roles.



Where would be the best place to put salary information? Each course/role/person combination has its own salary. For example, for a given course, a person is paid x to be the instructor and y to be the reader for that same course. For another course, the same person is only paid to be the instructor. I need to be able to populate a contract for each person using a mail merge.

My tables are

tblPeople
tblRoles
tblCourses
All of these have a foreign key in the junction table:
tblCoursePeopleRoles

Should I create a tblSalary and link it to the junction table? That way, each course/instructor/role combination has its own salary.

Or should I put the salary information in tblPeople or tblCourses? That doesn't seem right, but I am not sure why...

Thanks!

[orange]

If the Salary is dependent on all 3 - person, role and course - then I say it belongs in tblCoursePeopleRoles

[Rainlover]

Personally I would have a separate Table for this.

The main reason is that Salaries chage with the times. Therefore in the Salery Table I would have Role, Annual Salary or what ever is applicable to your situation. Finally an effective Date. So now you can have a different Salary for 2012 from 2011 and keep a history.

[alpinegroove]

If I put it in tblCoursePeopleRoles or in tblSalary linked to it how would I set up my mail merge?
Each instructor will have multiples records that contain salary information, right? How do I get them all into one record so that the merge knows they belong with the same person?
An append query?

[Rainlover]

You need to have your table structure correct before worrying about mail merge etc.

I can't address your question more fully as there is a lack of information on your structure.

[orange]

But you said different pay for different role in different course, no?

For example, for a given course, a person is paid x to be the instructor and y to be the reader for that same course. For another course, the same person is only paid to be the instructor.

If Salary changes with time, you'll need to identify Salary for Role for Course with a StartDate -EndDate to identify that Salary for that Role for that Course in that Time period.

[Rainlover]

Orange

End Date is Redundant as the Start Date of one period is the End Date of the previous.

[alpinegroove]

Thank you for your responses.
This is what I have so far in terms of structure:

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-txtAddress

tblRoles
-pkRoleID primary key, autonumber
-txtRole

tblCourses
-pkCourseID primary key, autonumber
-lngCourseTitle
-txtCourseName

tblCoursePeopleRoles
-pkCoursePeopleRoleID primary key, autonumber
-fkCourseID foreign key to tblCourses
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles

As for salary changing over time, let me explain a bit more about the nature of the school:

Each year we begin anew with new students and often new instructors. We start with new data each year, therefore.

A sophisticated database could probably accommodate that, but what we plan to do at this point is a start with a clean database every year.

We'll still be able to use some of the data from a previous year, but I don't need to worry at this point about end and start dates.

So the question is still where to put the salary figures in a way that allows me to later merge them into a contract template.

Thanks again!

[Rainlover]

In keeping with your current structure I would add another table and place the foregin key in tblCoursePeopleRoles.

I feel that there must be more to your Database than what you have described. But if this is all the Data you intend to store then your DB is very simple and the adding of a Salary table will complete the picture.

You will need a Foregin key which is attached to tblCoursePeopleRoles.

If there is only one Salary for each roll then you could include the salary within the Role table instead of a separate Table.

[alpinegroove]

If the Salary is dependent on all 3 - person, role and course - then I say it belongs in tblCoursePeopleRoles

The salary for Instructor (Lecture) and the salary for Teaching Assistant (Discussion) vary. There isn't a uniform salary for either, so I would like to be able to enter those manually for each Instructor based on what lecture course he or she teaches and whether they are also teaching discussion sections as "Teaching Assistant" for the same class.

If I put the compensation information in tblCoursePeopleRoles, what kind of query would I have to create to get something like this:

Name | Course | Lecture Compensation (Instructor) | Discussion Compensation ("TA") |

John Smith | Math 1A | $5000 | $2500
John Smith | Math 32 | $3500 | $3000

[orange]

Orange

End Date is Redundant as the Start Date of one period is the End Date of the previous.

Probably but not necessarily if they have individual contracts with Instructors. Moot point perhaps, but salary periods, like contracts have start and end dates. And, I can't speak for your country, but in Canada, lots of government contracts (municipal, provincial and federal) are not "settled" on the contract end date and often the status quo (pay and benefits) continue until a new contract is ratified. Then of course,they have to work out pay and benefits back to the Start Date of the "current" contract.
I don't have a problem with one period ending when the next starts, as long as everyone understands that's how it's set up.

[alpinegroove]

I feel that there must be more to your Database than what you have described. But if this is all the Data you intend to store then your DB is very simple and the adding of a Salary table will complete the picture.

This "database" used to be in one flat table. We used Access mostly for the convenience of having forms and queries. For obvious reasons, I have decided to make this database relational.

The main step was using the Roles model since each person can teach multiple courses and have multiple roles within each course.

Each course has three components: Lecture (taught by Instructor), Discussion (taught by Teaching Assistant), and Reader (done by Reader).

In some courses, one person is both Instructor and Teaching Assistant or Instructor and Reader. In other cases, a course has one person teaching Lecture (as Instructor) and another person teaching Discussion (as Teaching Assistant).

If there is only one Salary for each roll then you could include the salary within the Role table instead of a separate Table.

There isn't a uniform salary for any course, person, or role. The salary is adjusted manually for each combination.

You will need a Foregin key which is attached to tblCoursePeopleRoles.

Which direction will this go in? Should I create tblSalary with SalaryID and CoursePeopleRolesIDFK or should I put a SalaryIDFK in tblCoursePeopleRoles?

Sorry, I am pretty new to this and am getting confused...

The final goal is to be able to populate a contract with compensation, course location, meeting times, and contact information based on a query.

If an instructor has teaching responsibilities in more than one course, they would get a separate contract for each course.

I still don't understand what kind of query would provide all of that information.

[alpinegroove]

Probably but not necessarily if they have individual contracts with Instructors. Moot point perhaps, but salary periods, like contracts have start and end dates. And, I can't speak for your country, but in Canada, lots of government contracts (municipal, provincial and federal) are not "settled" on the contract end date and often the status quo (pay and benefits) continue until a new contract is ratified. Then of course,they have to work out pay and benefits back to the Start Date of the "current" contract.
I don't have a problem with one period ending when the next starts, as long as everyone understands that's how it's set up.

For a number of reasons, none of this is an issue in my case. I start with new data(base) every year and all of the contracts begin and end on the same dates.

[Rainlover]

You have conflicting points of view.

Orange has set terms of engagement and I have the ability to end the agreement mid term.

It is important that you look at both. By doing so you should end up with the model that suits your needs.

[alpinegroove]

Perhaps I am missing something, but I don't think this issue affects where to put the compensation given that I have a separate database each year.