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

    Database/ERD Feedback



      Database/ERD Feedback


      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.


      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!


        • 1. Re: Database/ERD Feedback

          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

            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


            Employee_Benefit:: _benefitIDfk = Benefits::__benefitIDpk


            but if not, please let me know.