2 Replies Latest reply on Oct 3, 2011 7:09 PM by eibcga

    Employee promotion tracking



      Employee promotion tracking


      Hello all, a FMPA11v4 Mac beginner user here trying to design a human resources database to track employees and their promotions throughout their employment in a large publicly traded company. Attached is the ERD which I hope has the right idea.

      An employee can receive many job ratings, and a rating can be shared by many employees. An employee can have many job positions, and a job position can have many employees. A department can have many locations, and a location can have many departments. A department can have many positions, but each position can only have one department. A division can have many departments, but each department can have only one division. A division can have many cost centres, but each cost center can only have one division.

      Now that I have set up all these one-to-one, and many-to-many relationships using join tables, how do I know which layout to start entering records? Since it appears that each layout's context can access related records from each of the other related tables many "hops" away, is there a best layout to choose? I guess this would depend on my needs and which point of view to run reports from. But my question is about data entry.

      I figured I'm supposed to just pick the Employee layout based on the Employee table, and start adding all the various portals to all the various join tables (i.e., Employee_Rating, Employee_Position, Dept_Location), then add child records to each portal?


        • 1. Re: Employee promotion tracking

          Side question: How many 'Ratings' are there?  How many different 'Departments' are there?  Let me suppose there are around 30, max, each. What difference would it make if those were not Tables, but simply value lists?  Likewise for 'Divisions', etc.  How many Locations, Divisions, departments, and ratings can an employee have at any instant in time?

          I think what you may need is basically an employee table, with one record per promotion.  (And even then that's only because you want to track their promotion history.  If you were happy to simply know where they are now then you would have one record per employee.)

          I may of course be completely missing the point but, for example, what function do you think wouldn't work if each employee record showed their Division, Department, Location, Rating, etc, at that time?

          • 2. Re: Employee promotion tracking

            Perhaps I'm overcomplicating the problem.  There are about half a dozen types for each of ratings, departments, divisions, etc.  An employee can not have more than one of these at any instant in time... but can be transferred from one to another during one's career.  I like your suggestion of just having one employee table and add a new record for each promotion so that we can track promotions of various employees over time.  Using value lists (with custom values, instead of values from fields) should also be good enough, since these values won't change over time, and the cost of this redundant data would not be significant.