Calculate the salary of employees

Posted by Sriharim under Sql Server on 5/26/2015 | Points: 10 | Views : 643 | Status : [Member] | Replies : 3
Design database (tables) for below scenario (to Calculate the salary of employees )

Organization has various ranges of products. In organization, CEO is the first employee, under CEO 4 managers are there, one manager is for sales, under sales manager 5 assistant managers are there, under each assistant manager team leaders are there, under each team leader sales executives are there.

Salary components are fixed salary + incentives amount + quarter bonus +birthday gift/bonus. Sales executive’s incentive depends on total sales in that month. Assistant managers are eligible for quarter bonus depending on his team performance(total sales). All permanent employees are eligible for birthday gift/bonus.

Please suggest database design to calculate the salary of employees. ( Database diagrammatic approach is also appreciated).

---
Srihari



Responses

Posted by: Bandi on: 5/26/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
check the attached for design.....
 Download source file

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 5/26/2015 [Member] Starter | Points: 25

Up
0
Down
when i showed similar design, they asked me which is the master table.
------
Please tell me, What is master table here ?
Including CEO will be in tblemployee ??
, isn't required to have master table(to store CEO ) in database .

In tblsalary component, ID column is a surrogate key ??

isn't required to have separate table to maintain/calculated incentive data ?



---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 5/26/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
tblDesignation is the master table in the design..

we can store fixed salary as part of tblEmployee table... Also store remaining components like bonus, b'day gifts and so on in separate table by mapping with EmpID of tblEmployee table



CREATE TABLE tblDesignation ( DesignationID INT identity(1,1) Primary Key, DesignationDesc varchar(200))
--CEO
--SalesManager/Manager
--AssistantManager
--Team Lead
--SalesExecutive

CREATE TABLE tblEmployee( EmpID int identity(1,1) Primary Key, Name varchar(100), DateOfJoining datetime, Salary dec(10,2), DoB datetime, EmailID varchar(200), PhoneNumber VarchaR(15), ManagerID int references tblEmployee(EmpID), DesignID int references tblDesignation(DesignationID))

-- 1 Chandu 52000 SalesExecutive/Employee
-- 2 Sailu 20000 AssistantManager
-- 3 Manohar 20000 Team Lead
-- 4 Muni 80000 CEO

CREATE TABLE tblSalaryComponents ( ID INT IDENTITY(1,1) Primary key, EmpID int references tblEmployee(EmpID), VariablePayAmount dec(8,2), VariablePayDesc Varchar(100))
-- 1 1 350 birthday gift
-- 2 1 2900 Quarter Bonus
-- 3 2 20000 Incentives


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response