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

[ahmedsa]

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

This can be as simple or as complicated as you need.

Weigh up the permanence of your schema if you decide on the 'big table' scenario. Will you be adding different types of allowances in the future? If not, a big table approach would be a simple prospect. The only issue I see with it is payment of the rent, although that could be sorted with an additional field such as "firstmonth" which if NULL or 0, would suggest that rent was paid monthly. Otherwise you could have integers 1-6. 1 = Jan and July, 2 = Feb and Aug up to 6 = Jun and Dec.

You could make the 'firstmonth' even more flexible with bit numbers (1=Jan,2=Feb,4,8,16,32...2048=Dec) and store all months where that rent payment should be made, e.g. a quarterly payment for somebody starting in January:

1 (Jan) + 8 (Apr) + 64 (Jul) + 512 (Oct) = 585

If they start in March:

4 (Mar) + 32 (Jun) + 256 (Sep) + 2048 (Dec) = 2340

A default value of 4095 would represent every month (or just leave 0 or NULL)

That's a simple schema - no relationships. But as we know systems grow. Systems change. What if a whole new raft of allowances become available, all with different payment schemes (quarterly, yearly etc).

The simple schema will no longer suffice - a related table (or multiple tables) should be created.

EMPLOYEE:
id
name
surname
email (etc)

I wouldn't place any payment/salary info should be placed in the EMPLOYEE table.

ALLOWANCES:
id
label
default_amount (maybe?)

EMPLOYEE_ALLOWANCES:
employee_id
allowance_id
amount ($£!)
payment_type (e.g. the payment schedule as described with "firstmonth" above)
active_date (e.g. from when this to be implemented)
inactive_date (e.g. from when this to be stopped)

The pivot table or link table allows the storage of historical data and prevents it from being overwritten - this is due to the active_date and inactive_date. THe inactive_date would be left blank when an allowance is set up initially. If the allowance is then changed to a higher (or lower!) amount
a new record is added to the table, and an inactive_date placed on the now defunct record.

Some would argue, why have an inactive_date? Surely a new record with the same employee_id and allowance_id and more recent active_date would supercede older versions. And yes that would be the case. However, say a new allowance was added to the employee like 'fuel allowance', but they were already claiming 'metro allowance'. Company policy may dictate that they could have one but no the other, so you'd have to inactivate the 'fuel allowance' so they wouldn't be paid both allowances. This way you still have historical data stored.

Anyway there are many ways to do this and they depend upon your specific needs. I'm not claiming that the ideas above are what you should use, rather just showing different approaches which you may adopt in part or in total.

Oh, just thought, I don't know where you are, but some allowances may be taxable, some may not be. Salaries may have sliding scales of income tax (and other deductions) - so even more tables could be required.