February 13, 2015 at 3:26 pm
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
February 16, 2015 at 7:39 am
ahmed_elbarbary.2010 (2/13/2015)
Hi guys I have problem i need to make ERD relation entitybetween 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
There is not much detail here since all you listed was a couple columns and then deep into requirements but I would suggest you keep the allowance as a separate table. Probably 2 tables actually. You would need an allowance table but you also need to know which AllowanceType a given row is so I would create a second table for AllowanceType. If you cram those three allowances as columns in your employee table you are painting yourself into a corner. Think about what would happen when a fourth type of allowance is needed. You would have to add more columns to employee. Keep your data normalized and avoid the hassles down the road.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply