Add allowance to employee table is wrong or true according to my case

[ahm1985]

Hi guys I have problem i need to make ERD relation entity
between employee and allowance
Employee table
Name
address
Basic Salary
Bonus

Allowance table
House rent
Food Allowance
Moving Allowance

Basic Salary is monthly and fixed
Bonus is monthly and fixed
food allowance is monthly and fixed for married employee
House rent is monthly and fixed for some employee and some employee take house rent two time in year every 6 month
every employee married take 3 months salary from basic salary in year
suppose i m married and i take basic salary 5000
i will take rent 5000 x 3=15000/12=1250 monthly
some employee take rent every half year meaning every 6 month
meaning 15000/2=7500


My question according to my case above
Which is best put allowance in table allowance or put allowance(food,housing,moving)
in employee table and what relation between two tables

 

[TamarGranor]

Since it seems possible that someday, there might be another kind of allowance, you definitely don't want to put this in the Employee table. I think you want an Allowance table, where each row represents one type of allowance for a particular employee. You'll need some fields to indicate how this particular allowance applies to this particular employee. Something like this:

EmployeeID
AllowanceType (probably a look-up into a separate table of allowance types)
Amount
Frequency

The basic idea is that you should be able to calculate each allowance for each employee without having to know all those rules you listed. They should be encoded in the data.

Tamar

P.S. I can't imagine where you are in the world where you're allowed to treat married and single people so differently.