Design Suggestions Needed (For an Employee Database)
-
I need to redesign an existing employee database. The database in question mainly proforms production reporting.
My problem is how to accurately reflect typical company hierarchies. In our company, the structure goes something like this:
Employees Make up teams
Teams make up Divisions
Divisions Make up Business Channels
Business Channels make up a Company
...
What I initially came up with is something like this:
Employees EMPID int identity pk FNAME varchar(30) LNAME varchar(30) STATUSID int fk Employee_Groups GROUPID int identity pk DESCRIPTION varchar(30) TYPEID int fk Group_Types TYPEID int identity pk DESCRIPTION varchar(30) Employee_Group_Xref EMPID int pk GROUPID int pk So how it works is that table "Group_Types" has data like "TEAM", "DIVISION", "CHANNEL". Then all that needs done is to create groups like "Production 1" of TYPE "TEAM", and then add employees and the corresponding group to the Employee_Group_Xref table.The problem with this is that instead of the model I listed above, each group type is actually comprised of individuals.
For instance, if a new employee is added, a record would have to be added to Employee_Group_Xref for each applicable group type ("TEAM", "DIVISION", "CHANNEL").
That would still be workable except for the fact that I know I would be asked for reports for "Division X", broken down by TEAM. Not to mention the fact that it would be a housekeeping nightmare the way it is currently designed.
Any ideas or articles around dealing with this sort of situation?
-
Employees EMPID int GROUPID int GroupHierarchies GROUPID int PARANTGROUPID int DESCRIPTION varchar GroupHierarchies table can hold your company structure.
-
How about this:
EMPLOYEE TABLE
EmpID (primary key)
LName
FName
TeamID ... foreign key to Team Table
TEAM TABLE
TeamID (primary key)
TeamName
DivisionID ... foreign ID to Division Table
DIVISION TABLE
DivisionID (primary key)
DivisionName
ChannelID ... foreign key to Channel Table
CHANNEL TABLE
ChannelID (primary key)
ChannelName
I know it's "hard-coding" the hierarchy, but querying would be easy using joins. Also, all one would have to do is assign an employee to a team, and the rest is already in place.
Dana
Connecticut, USA
Dana -
This is actualy a question for responder wc700, but was wondering if you had some skeleton SQL to do a basic sum operation if one wanted say a report with totals at each level of the hierarchy. Looks to me to involve recursive queries & joining the hierarchy table to itself?Thx!
maddog -
I think DanaH1976 had the right idea; assuming your company's structures stay static and they don't re-shuffle management/groups on a very regular basis, that solution will provide you with the best performance and a logical model closest to the real business you're reporting about. On the other hand, if you have more frequent changes (especially of a hierarchical nature, e.g. new levels being added or subtracted) you might want to look at something closer to wz700's solution, or perhaps do a Google search for "Nested Sets Model," which is a way of modelling hierarchies very flexibly (it provides excellent select performance with no recursion or self-joining necessary, at the cost of a huge insert penalty for re-building the hierarchy).
--
Adam Machanic
whoisactive -
I'd definitely vote for a flexible model, like the one proposed by wz700. You can bet there will be changes over time... as soon as you hardcode the structure, some new boss will want to show how great ideas he/she has and will come up with another level between Team and Division
. If that should happen, hierarchical structure does not require any structure changes, you just add new level and redefine parents... and add that level to your reports, of course - but that shouldn't be such a big nuisance.Querying the hierarchical structure is not too complicated either (just a few self-joins on the GroupHierarchies table), so you should be able to produce reports grouped by any level without problems.
-
regardless of which way you go ... add an effective date to all so that in the end you can generate comparative reports ( ie prior period headcount to current period headcount) and get your prior period numbers to reflect the hierarchical relationships that existed at that time.
You can count on management making organizational changes.
Doyle
-
BOL article "Expanding Hierarchies" may help.
-
Thanks for the responses. The structure is already hardcoded, so i'm trying to get away from that.
I have one additional issue, and I believe it will fit in just fine to the proposed model. The issue is that there is also a group type "CUSTOM", which can mix and match employees from different teams. It's mainly used to group employees together that do similiar jobs, but are in different parts of the company.
Side note, but this forum doesn't play well with mozilla
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply