Adding an IsMain Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Adding an IsMain Column

Author

Topic

CoopDeveloper
Starting Member

4 Posts

Posted - 2013-06-21 : 10:02:22

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

52326 Posts

Posted - 2013-06-21 : 10:10:18

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-21 : 10:13:44

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.

CoopDeveloper
Starting Member

4 Posts

Posted - 2013-06-21 : 10:22:23

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-21 : 10:31:22

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?

CoopDeveloper
Starting Member

4 Posts

Posted - 2013-06-21 : 10:42:43

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-21 : 11:15:05

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 11:18:16

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

CoopDeveloper
Starting Member

4 Posts

Posted - 2013-06-21 : 11:47:40

Thanks everyone for your help! Problem solved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources