4 Replies Latest reply on Dec 4, 2013 9:51 AM by JonathanOxborrow

    Relational Design Question

    JonathanOxborrow

      Title

      Relational Design Question

      Post

           I'm making a database to record jobs, employees, and other information.  The problem is that each employee can have a different rate they change depending on the type of job.  For example: if the job is a type 1 then the employee charges x/hour, but if it's a type 2 job then the rate is y/hour.  How can I make a relationship that will join rate to both job type and employee rate for that type?  I'm using filemaker pro 12.

        • 1. Re: Relational Design Question
          philmodjunk

               An employee can link to many different job types and a given job type can be linked to many different employees. Thus, this is a many to many relationship.

               Start with these relationships:

               Employees-----<Employee_JobType>-----JobTypes

               Employees::__pkEmployeeID = Employee_JobType::_fkEmployeeID
               JobTypes::__pkJobTypeID = Employee_JobType::_fkJobTypeID

               You can place a portal to Employee_JobType on the Employees layout to list and select a JobTypes record for each given Employees record. Fields from JobTypes can be included in the Portal to show additional info about each selected JobTypes record and the _fkJobTypeID field can be set up with a value list for selecting JobTypes records by their ID field. Your rates would be stored in JobTypes and these values would be auto-entered into a corresponding field in Employee_JobType each time you create a new record in Employee_JobType and link it to a JobType record.

          • 2. Re: Relational Design Question
            JonathanOxborrow

                 I'm still a little confused about how the different rates will know which employee and job type they are linked to.  Each employee can have 2 or more rates each dependent on the type of job.  How will I show that in jobTypes?

            • 3. Re: Relational Design Question
              philmodjunk

                   Do all employees with the same job type get the same rate? If so, put the rate in JobTypes. If not, put the rate in Employee_JobType.

              • 4. Re: Relational Design Question
                JonathanOxborrow

                     Alright. Thanks for the help.