2 Replies Latest reply on Jul 31, 2012 2:42 PM by lorigar

    Database/ERD Feedback

    lorigar

      Title

      Database/ERD Feedback

      Post

      Hello -

      I am new to FileMaker and am creating a database for our HR manager.    I have the Employees function working fairly well, and am now getting ready to add the tables to track Employee Benefits.   I've attached an ERD and am looking for some feedback to see if I have structured these tables correctly.

      Requirements:

      1. Track benefitTypes Medical, Dental, Flexible Spending, Life, AD&D, Short Term Disability, Long Term Disability for each employee including:
        1. Election type - employee only, employee and spouse, employee and children, employee and family, waived
        2. Employer cost - calculated based upon employee cost for this benefit (employer pays 100% of employee and 50% of spouse, children cost)
        3. Eligibility Date - not sure to handle this as it will be different depending upon the benefitType
      2. I'm unsure what to do with the Beneficiaries table.   We have two different life insurance providers, and most employees have a policy with each provider and can designate multiple beneficiaries for each policy. 

      I know I will have to do some playing around with this, but would like to get it as "right" as possible starting out.

      I've attached a screenshot of the ERD and would appreciate any feedback........specifically on the tables/relationships relating to Employees and Benefits, but I"m open to general design feedback as well.

      Thanks a ton!

      ERD_7.31.12.png

        • 1. Re: Database/ERD Feedback
          philmodjunk

          Seems like employees to Benefits might be a many to many relationship.

          If so, a join table between the two would be useful.

          That way, you can have one record for each policy with a beneficiary and you can link beneficiaries to the specific policy.

          • 2. Re: Database/ERD Feedback
            lorigar

            Thanks for the feedback.   Sorry, I should have mentioned in my initial post......the Employee_Benefit table is a joins the Employees and Benefits tables.    I've watched the filemaker relational database series on Lynda.com and think I have it set up properly, with:

            Employee_Benefit:: _employeeIDfk =  Employees:: __employeeIDpk

            and

            Employee_Benefit:: _benefitIDfk = Benefits::__benefitIDpk

             

            but if not, please let me know.

             

            Thanks!