December 4, 2014 at 1:09 pm
I already make payroll database i have all tables but one relation in this database i don't know how to make relation
between payroll table and deduction and income
Employee Table
Employee ID
Name
Address
Nationality
Salary Table(1 to 1 Employee Table)
Salary ID
Employee ID
Salary
Income Table(N to 1 Employee Table)
Income ID
Employee ID
Bonus
Over Time
From Date
To Date
Deduction Table ( N to 1 Employee Table)
Deduction ID
Employee ID
Deduction
From Date
To Date
Payroll Table(I don't know Relation)
And this is my question how to collect Deduction and income from two tables
and make relation to Payroll table
Meaning what relation between Payroll and( Deduction And Income)
How to collect two foreign key of income and Deduction in Payroll Table
Are relation one to many or what
December 5, 2014 at 4:55 am
can any one help me in question above
December 5, 2014 at 10:36 am
Given where you're having the deductions table, I am assuming those are actually the default deductions that might apply on a paycheck by paycheck basis.
Assuming that's true - I'd envision you'd have a "many to many" relation between "payroll" (which tracks what actually is getting paid out during each payroll period) and deductions. So you'd have a mapping table, perhaps called "applied deductions", which links the payroll period to the deduction type.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 5, 2014 at 2:30 pm
Thank you for reply
My data as following(fixing data)
Salary (Change every year)
Bonus(change every year)
Transport Amount
Resident Amount
My data Variable data
overtime
Deduction
How my diagram will be
my diagram no as following
but remaining Payroll table
Employee table
Employee ID
Salary
Bonus
Transport Payment
Resident Payment
Variable Data table
Deduction
Overtime
Employee ID
Commission
Payroll table
Employee ID
December 6, 2014 at 2:21 pm
A couple of issues:
1. salary changes over time. At every least you'd have a to/from, and the relationship would be employee (1-->N) salary .
2. you'd probably want a separate payrollPeriod table which establishes the frequency. PayrollPeriodID + To/From at least. You might also have different items on different frequencies so you might want to account for that.
Assuming your Payroll table represents each payroll transaction, it might look something like:
PayrollID
EmployeeID
PayrollPeriodID
SalaryID --<-- not strictly necessary since you could derive it from the other 2 FK's. Still commonly added in.
Your income and deductions would then be associated to the payrollID they're being paid in. I would usually have a separate table to do the correlation, since the assumption would be that you might have multiple deductions or income in a given period.
So the income would be looped in perhaps as such:
AdditionalIncomePaid table
IncomeID
PayrollID
--additional columns as needed
And the deductions would be associated in the same fashion.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply