Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Database Design help for employee benefits database

    I created a database to store various employee benefits like medical, dental, voluntary life, short term disability, etc. I setup each benefit type in a separate table for the plan information (name, costs, plan specific details) and a separate table for storing an employee election (e.g. single, family, short term disability class, etc.).

    The issue I am having is I need to pull all these together to calculate total premiums, costs, etc. I would prefer to have a single "plans" table and a single "elections" table but the different benefit types have different fields. For example, Medical has a choice of plan name, eligibility, and number of children over 18 on the plan. Short term disability has a choice of class, buy-up (y/n), etc.

    What is the best way to design a database with entities that have similar attributes and also have different attributes that need to be tracked?

  2. #2
    Join Date
    Jun 2009
    Posts
    3
    You're probably best off to see which information recurs.

    For example:
    student
    studentID
    street
    houseNumber
    postalCode
    city
    state
    class

    teacher
    teacherID
    name
    street
    houseNumber
    postalCode
    city
    state
    subject

    as you can see the following items are present in both tables:
    name
    street
    houseNumber
    postalCode
    city
    state

    So you can solve it in the following way:
    address
    addressID
    name
    street
    houseNumber
    postalCode
    city
    state

    student
    studentID
    addressID
    class

    teacher
    teacherID
    addressID
    subject

    addressID is a foreign key in the teacher and student table to the address table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •