SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Adding an IsMain Column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CoopDeveloper
Starting Member

4 Posts

Posted - 06/21/2013 :  10:02:22  Show Profile  Reply with Quote
I have some tables to keep track of employees who work at apartment buildings. Some of the positions are Custodian, Emergency Response, Maintenance, etc. It is ok if there are two or more people in the same position, such as two custodians. However, if there are two or more people in the same position at a building, I would like to be able to designate one person as the main person for that position. This will be the person who shows up on the contact list for the apartment building.

Here are the tables:

AptBuilding
===================
pk BuildingID int
BuildingName nvarchar(50)

Employee
===================
pk EmployeeID int
FirstName nvarchar
LastName nvarchar
Phone nvarchar
IsMain bit
fk BuildingID int
fk PositionID int

EmployeePosition
===================
pk PositionID
PositionName

Is adding an IsMain column to the Employee table the best way to keep track of who the main person is for each position at each building or is there a better way.

My main goal is to get a list of all the maintenance people for all the buildings and only have one maintenance person show up on the list for each building.So if there is only one maintenance person for a building, then that one would show up on the list. If there is more than one maintenance person, then the main one would show up on the list and if there is more than one and none of them are marked as the main then the one that was entered first (the lowest EmployeeID) would show up on the list.

Thanks for your help!

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/21/2013 :  10:10:18  Show Profile  Reply with Quote
In my opinion it should be in separate table altogether which will just have EmployeeID,BuildingID,PositionID and IsMain. In that way you dont have to repeat employee details in cae an employee holds more than one position ie customdian for one, er for other etc. It will also avoid update anamolies for employee details.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 06/21/2013 :  10:13:44  Show Profile  Reply with Quote
I think it would be better to have another table that relates employees, buildings and positions and then have the IsMain in that table.
THe link table would have the following columns:
BuildingId
PositionId
EmployeeId
IsMainContact

You may have additional columns, for example effective dates, or hours or whatever else you may need in that table.

I can go through the advantages of doing it this way if you like, but try some use cases and you will see why it is better to separate this information out into a different table.
Go to Top of Page

CoopDeveloper
Starting Member

4 Posts

Posted - 06/21/2013 :  10:22:23  Show Profile  Reply with Quote
OK, so I can break off the employee details into a separate table and just use a foreign key. That's no problem.

Now the issue is how do I maintain the table to make sure there is only one person for each position at each building that is marked as main? Also, how would I write my query for the maintenance person report?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 06/21/2013 :  10:31:22  Show Profile  Reply with Quote
You can add a unique filtered index on buildignid and Ismaincontact. Filter on IsMainContact=1.

As for the maintainence person report: What do you want to see in the report?

Edited by - James K on 06/21/2013 10:31:56
Go to Top of Page

CoopDeveloper
Starting Member

4 Posts

Posted - 06/21/2013 :  10:42:43  Show Profile  Reply with Quote
quote:
Originally posted by James K

You can add a unique filtered index on buildignid and Ismaincontact. Filter on IsMainContact=1.

As for the maintainence person report: What do you want to see in the report?



I'll definitely check out the filtered index. Thanks.

For the report, I just need the BuildingName, FirstName, LastName, and Phone where the PositionName = "Maintenance".

The tricky part is that I can only return one maintenance person per building. So if there is only one maintenance person for a building, then they would be on the report. If there is more than one maintenance person for a building, then the one that is IsMain would be on the report. If there is more than one maintenance person for a building and none of them are IsMain, then the one that was entered first would be on the report, so the one with the lowest ID.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 06/21/2013 :  11:15:05  Show Profile  Reply with Quote
The query would be something like shown below - I think I have written the logic correctly, but if you see that it is not returning what you are expecting, post some sample data that shows the problem
SELECT
	b.BuildingId,
	b.BuildingName,
	p.PositionID,
	p.PositionName,
	e.EmployeeId,
	e.EmployeeName
FROM
	Buildings b
	INNER JOIN TheNewTable t ON
		t.BuildingId = b.BuildingId
	INNER JOIN Positions p ON
		p.PositionId = t.PositionId
	CROSS APPLY
	(
		SELECT TOP (1) EmployeeId, EmployeeName 
		FROM Employees e
		WHERE e.EmployeeId = t.EmployeeId
		ORDER BY
			CASE WHEN IsMainContact = 1 THEN 1 ELSE 2 END,
			EmployeeId
	) e			
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/21/2013 :  11:18:16  Show Profile  Reply with Quote
how do I maintain the table to make sure there is only one person for each position at each building that is marked as main
you can enforce this by means of an instead of trigger or check constraint

how would I write my query for the maintenance person report

something like

SELECT *
FROM 
(
SELECT e.FirstName,e.LastName,e.Phone,ep.PostionName,a.BuildingName,
ROW_NUMBER() OVER (PARTITION BY a.BuildingID ORDER BY IsMain=1 THEN 0 ELSE 1 END,e.EmployeeID) AS Seq
FROM Employee e
INNER JOIN EmployeeBuildingPosition ebp
ON ebp.EmployeeID = e.EmployeeID
INNER JOIN EmployeePosition ep
ON ebp.PositionID = ep.PositionID
INNER JOIN AptBuilding a
ON a.BuildingID = ebp.BuildingID
)t
WHERE Seq=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 06/21/2013 11:29:18
Go to Top of Page

CoopDeveloper
Starting Member

4 Posts

Posted - 06/21/2013 :  11:47:40  Show Profile  Reply with Quote
Thanks everyone for your help! Problem solved.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000