If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Database Design help for employee benefits database

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-25-09, 16:30
eandrews eandrews is offline
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old 06-27-09, 16:04
Simon_Verhoeven Simon_Verhoeven is offline
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On