December 22, 2007 at 3:02 pm
Hello All,
First, I would like to say that I have found these forums very helpful. I'm very impressed with the wealth of knowledge, expertise and professionalism that are displayed here in these forums. I apologize if I should have posted in the beginner topics but I thought this forum may be better related to my questions.
I'm faced with a task of creating a reporting tool for my department using SQL2000, ASP.NET and VB.NET. My "road block" at the moment is how to design the tables for the Employee to Team/Supervisor Relationships and I'm hoping someone may be able to offer me some advice on the best way to do this.
My issue isn't so much how build the tables but how to handle "Historical" reporting relationships and Employee performance data. For example, I have Employees A,B, and C currently reporting to Supervisor A. All of their performance data will currently roll up Supervisor A. However, next month Employees A,B and C will then report to Supervisor B and their performance data now roll up to Supervisor B. Supervisor A has a new set of Employees assigned to him (Employees D,E and F). At the end of the month or year, how can I ensure that if I pull a report for Supervisor A that I will retrieve all the Employee performance data for ALL the employees that had reported to Supervisor A at any given time frame, versus pulling a report at the end of the month/year and only retrieving the Employee performance data for those Employees who are CURRENTLY assigned to Supervisor A?
Another example is that Employee A was assigned to Supervisor A for half of the month and then assigned to Supervisor B for the rest of the month. If I pull a Monthly report, how can I ensure that Employee A's first half of the month performance data will stay associated to Supervisor A and the second half of the month will be associated with Supervisor B? What I envision that will happen, is that when I pull the Monthly report, ALL of Employee A's performance data will roll up into Supervisor B and nothing will display on Supervisor A's report for that month.
Maybe I'm making this harder than it really should be. I haven't built any tables yet, I want to make sure that I have proper "architecture" laid out before I start creating the tables.
Any help/advice would be greatly appreciated.
Thanks!
December 22, 2007 at 3:12 pm
Two ways,
One is to create a link table, has EmpID and SupervisorID, along with dates that correspond to the timeframes. For current assignments, leave the end date open and handle that as the current date, ifnull(enddate, getdate()), or something similar. This way you can be sure you report on the structure as it was.
Time reporting like this can be complicated, so you can also denormalize the data once the relationship changes, so store off certain data when an employee moves, and then join that back in later.
It really depends on how much data you need and to what extent you need to keep data historically. You could add columns to the link table to store data that needs to be kept and could possibly change in the rest of your structure.
December 22, 2007 at 4:01 pm
Thank you for the quick reply. I appreciate it. I will go the route of creating the link tables.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply