Multi employer to employee manage

问题: Hope you are doing good. I am facing issue in multiple employer to employee. What's going on my mind: Employer table employerID, fname, lname, created_at employee...

问题:

Hope you are doing good. I am facing issue in multiple employer to employee.

What's going on my mind:

Employer table

employerID,
fname,
lname,
created_at

employee table

 employeeID
    fname
    lname
    creted_at

This is structure of basic tables then after i created separate table for relation for multi employer to employee.

employer2employee Table

employer_id,
employee_id

salary table

salaryID,
salaryAmount,
salaryDate

Now i am facing an issue for salary recording. Which ID should be prefer here? i want to get salary each employee, employer wise. Please help me. Thanks for the help.


回答1:

You can do it like this, Table_Employee :: EmployeeId | FName | LName | CreatedDate | FkSuperEmployeeId

Here FkSuperEmployeeId will get Id of employee from same table

Table_Salary :: SalaryId | SalaryAmount | SalaryDate

Now you can set FkSalaryId in Table_Employee table and if possible then you can set SalaryAmount and SalaryDate in Table_Employee.

So by this way you will be able to handle it easily.


回答2:

First, you can avoid 3 tables to store employer and employee relationship information.

Instead of 3 tables, use only 1 employee table and have a new column only, which will store the employeeID against the other employee and eployeeID i.e. that particular employeeID in new column is a employer (which is also a employee) of this employee.

Example -

enter image description here

So, Employee 1 doesn't have any employer as Employee_EmployerID is 0. Whereas, Employee 2 has a employer, who is employee 1.

In this case, you can add employeeID in your Salary table. And, by doing self join within the employee table, you can fetch out the required information.

NOTE: I will suggest to not add SalaryID in your Employee table, because employee table is a master table, So having salaryID inside the employee table can cost you security issue later if you share the employee table with some outside user.

SELECT E1.FName [EmployeeName], E2.FName [EmployerName], S.SalaryAmount
FROM EmployeeTable E1 
    LEFT OUTER JOIN EmployeeTable E2 ON E1.EmployeeID = E2.Employee_EmployerID
    INNER JOIN SalaryTable S ON S.EmployeeID = E1.EmployeeID

However, if you want to keep your structure same as it is with 3 tables for employee-employer relationship, then you can add SalaryID into employer2employee table OR add employeeID only in your Salary table. :)

In both the case, only joining style with the tables will change while fetch.

  • 发表于 2018-07-05 13:11
  • 阅读 ( 317 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除