Calculate the salary of employees

Posted by Sriharim under Sql Server on 5/26/2015 | Points: 10 | Views : 1251 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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).

Mark as Answer if its helpful to you
---
Srihari

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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 ? Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

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 ?

Mark as Answer if its helpful to you
---
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

0	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

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

Latest Posts