Page 1 of 4 1234 LastLast
Results 1 to 15 of 55
  1. #1
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114

    Where to Put Instructor Salary?

    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!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If the Salary is dependent on all 3 - person, role and course - then I say it belongs in tblCoursePeopleRoles

  3. #3
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  4. #4
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    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?

  5. #5
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  7. #7
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Orange

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

  8. #8
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114

    More info

    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!

  9. #9
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  10. #10
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    Quote Originally Posted by orange View Post
    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

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by Rainlover View Post
    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.

  12. #12
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    Quote Originally Posted by Rainlover View Post
    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).

    Quote Originally Posted by Rainlover View Post
    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.

    Quote Originally Posted by Rainlover View Post
    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.

  13. #13
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    Quote Originally Posted by orange View Post
    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.

  14. #14
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  15. #15
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    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.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating Net Pay for Salary & Hourly Employees
    By brbrooks73 in forum Queries
    Replies: 2
    Last Post: 08-16-2011, 12:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums