April 2, 2012 at 8:06 pm
I have two tables in a database which are named;
Employee - *empId, empFirstName, EmpLastName, empFKsalTitle (Foreign key-Salary table), supvID
Salary - *salaryTitle, monthlyRate, commissionRate
* = PK
I have 3 salary titles which are;
Sales person, Sales manager, District manager
I need to implement the following rules in the table;
Sales person can only report to a Sales Manager
Sales manager can only report to a District manager
Any suggestions on the best way to implement these rules?
Thank you!
April 3, 2012 at 3:16 am
do a foriegn key to the same table and pass in the right manager employee id for the employee, standard parent child relationship
April 3, 2012 at 8:49 am
anthony.green (4/3/2012)
do a foriegn key to the same table and pass in the right manager employee id for the employee, standard parent child relationship
of course if the employee id changes you would need to update the reports to column. another way is another table, EmpReportsTo. to use you would join to it and pull the childtitle as boss
CREATE TABLE EmpReportsTo (
ReportsToID INT IDENTITY(1,1)
ParentTitle VARCHAR(64),
ChildTitle VARCHAR(64)
)
INSERT INTO EmpReportsTo (ParentTitle ,ChildTitle )
SELECT 'Sales person' , 'Sales Manager' UNION ALL
SELECT 'Sales manager', 'District manager'
i would also look at creating a numeric id column on Salary so you are not joining on strings. not much difference just my personal preference on relating tables.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply