how to write a data table structure for the following scenario [Resolved]

Posted by Sudheep.Grandhe under Sql Server on 2/4/2015 | Points: 10 | Views : 349 | Status : [Member] | Replies : 3
Hi Team,

In a company more than 1000 employees are there in that hierarchy ceo,senior managers,team leaders,executives and the scenario is based on sales each executive will get the bonus for quarter and yearly.

Based on executive performance above levels will get bonus for quarter and year.how to design a table structure for this scenario.

Regards,
Sudheep.G.

Best,
Sudheep.



Responses

Posted by: Bandi on: 2/4/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Go through this table structure once and let know if any thing missed here

DesignationLevels --> maser data ( 1= CEO, 2= Senior managers, and so on)
Person --> PersonID, Name, DoJ, Address, PhoneNumbe, Email
Product --> Product details
Customer --> CustomerID, Name, address, email, PhoneNum, and so on
SalesPerson --> SalesPersonID ( refer Person table) , Bonus, CommissionPct, SalesYTD, SalesQuarter), SalesPersonLevel ( -->reference of DesignationLevels table)
SalesOrders --> SalesOrderID, ProductID ( refer Product table) , OrderDate, DueDate, ShipDate, OrderQty, UnitPrice, Status, CustomerID, SalesPersonID ( refer SalesPerson table)


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

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

Posted by: Bandi on: 2/5/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
For Primary Key (PK) creation

DesignationLevels -->  ID Primary Key, Level {  maser data ( 1= CEO, 2= Senior managers, and so on) }
Person --> PersonID ( PK) , Name, DoJ, Address, PhoneNumbe, Email
Product --> Product details { ProductID (PK), Name, UnitPrice }
Customer --> CustomerID (PK) , Name, address, email, PhoneNum, and so on
SalesPerson --> SalesPersonID (PK) ( refer Person table) , Bonus, CommissionPct, SalesYTD, SalesQuarter), SalesPersonLevel ( -->reference of DesignationLevels table)
SalesOrders --> SalesOrderID (PK) , ProductID ( refer Product table) , OrderDate, DueDate, ShipDate, OrderQty, UnitPrice, Status, CustomerID, SalesPersonID ( refer SalesPerson table)


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

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

Posted by: Sudheep.Grandhe on: 2/5/2015 [Member] Starter | Points: 25

Up
0
Down
Hi,

For which table we need to create a primary key and how to calculate bonus for quarter and half and yearly and how to write a query on the bais of sales.

Regards,
Sudheep.G.

Best,
Sudheep.

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

Login to post response