What relation of (income and Deduction) and payroll table

[ahm1985]

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

 

[gmmastros]

I would suggest that you should combine the deduction and income tables in to just one table. I assume you have OverTime to represent extra hours worked by the employee and Bonus represents a single monetary value. You can use a single table to represent deductions by allowing the values to go negative.

For example, if employee is sick, you can add a row with -8 for hours. If you deduct money for taxes, you could put that in the bonus column as a negative number.

Basically, it looks like Income is money you give to the employee, and deduction is money you take away. Since dollar amounts can be negative, one table would suffice.

Also... the salary table is a bit weird because it introduces a new column called SalaryId, but this column isn't used anywhere else, so what is the point? If you remove SalaryId, then you are left with EmployeeId and Salary. Given this, you should just add the salary column to the Employee table.

If you accept my suggestions, then your schema is reduced to 2 tables, employee and income. Also, your question would no longer be valid because you would not be collecting data from 2 tables (income and deductions) anymore. It would just be the one table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnherman]

Well, it's going to partly depend on Business Rules. If a person is out on Disability or on a Leave of Absence, do they still get Deductions for (say) Medical Benefits? Anyway, you get the idea. If yes, then there could be a Deduction record with no Income record.

Can a person get more than one Income record on each payroll? Does the company give Bonuses or pay Commissions? If yes, then there could be many income records per payroll record. Or, you could have multiple Payroll records with one payroll record for each income.

It depends on how you want to design it, which depends to some degree on the business rules. Especially the exceptions.

==================================
adaptive uber info galaxies (bigger, better, faster than agile big data clouds)

 

[ahm1985]

Thank you for reply
Yes my company have bonus and comission and overtime
can you tell me relationship what will be if payroll have more income
if possible help me

 

[ahm1985]

can any one help me

 

[johnherman]

Let me make some assumptions and maybe it will help you figure out what you need to do. Let's assume that the Payroll table is one-to-one with an Issued Check, or Issued Payment. Does your company give separate checks for commissions, bonuses, expenses, and salary? For instance, a person might be on salary, and get their medical, dental, vision, insurance, and other deductions taken out of their salary paycheck each pay period. That person might get a quarterly bonus, but there wouldn't be the above deductions from the bonus. However, there would be taxes, social security, and maybe other deductions from the bonus. Same for commissions and expenses.

We need more definition regarding the entity "Payroll". The big question you need to answer is whether an employee can get multiple Payroll checks per payroll time period. If you can answer that question, the rest should fall into place. If you can provide other attributes associated with "Payroll", it might be helpful in our (and your) understanding of that entity. What's a Payroll?

==================================
adaptive uber info galaxies (bigger, better, faster than agile big data clouds)

 

[gbaughma]

I think that you should be talking to the company accountant. I wouldn't even pretend to develop payroll tables without a thorough understanding (and a sign-off) from an accountant.

But, that's just me... I like to cover my back-side.


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[johnherman]

I think this was for a project for a course. The urgency during the Dec 4-6 timeframe has subsided considerably.

==================================
adaptive uber info galaxies (bigger, better, faster than agile big data clouds)