|
Hierarchies Part 2 |
|||
|
If you've been receiving my tips for more than a week, you may be wondering what happened to
Hierarchies Part 1. This article is actually a follow on from a previous article titled "Self Joins Part 1". One of the really neat things about owning the SQL-Guru site, is that there are people who will alert me whenever I post a sub-optimal solution. Consequently, this isn't the article I originally intended to write as "Self Joins Part 2". Suppose I have a company which has the following employees (hey, a girl can dream!): Adelle : Manager John : Assistant Manager Kate : Area Manager (Australia) Brian : Area Manager (New Zealand) Following the design pattern presented in my previous article, it would be tempting to set up a table like the following: Table : Employee Field1: EmployeeID autonumber primary key Field2: EmployeeName text Field3: BossID number This week, I'm only going to address one of the design flaws in this type of structure: This table is representing both the company structure and the individual employees, in the same table. Suppose John leaves the company. That would create a vacancy for an Assistant Manager, but there is no way of representing a vacancy in the Employee table, except by creating a special employee record with the name "Vacant" and the same BossID that John had. It gets worse than that. What if Kate is promoted to fill John's position? Her subordinates wouldn't automatically be promoted, but they will still have their BossID's pointing to her record, so they will all have to be updated to point to whoever replaces Kate as Area Manager for Australia. Although the company structure might change from time to time, it will generally be more static than the list of employees within that structure, so it makes sense to model the company structure and the employees in two separate tables. For the moment, I'll model the company structure using a table similar to the one I originally proposed: Table : Position Field1: PositionID autonumber primary key Field2: PositionTitle text Field3: BossID number Field4: EmployeeID number I'll put the employees in a second table, linked to the first. Table : Employee Field1: EmployeeID autonumber primary key Field2: EmployeeName text Field3: PositionID number Is this structure normalized? Strictly speaking, no. The missing piece to this puzzle lies in the fact that we are looking at the relationship between employees and positions as a one-to-one relationship. That might hold true at any one point in time (not counting any job-sharing arrangements), but over a period of time, it doesn't. To hold everybody's employment history, I'll add the following table: Table : EmploymentHistory Field1: StartDate Date Field2: PositionID Number Field3: EmployeeID Number Field4: Salary Currency The last field of both my Position and Employee tables are now redundant, since the link between these tables is now stored in the EmploymentHistory table. But don't rush out and modify the structure of your hierarchical databases just yet. There is still one more (even more fundamental) flaw to the design pattern that I previously presented (special thanks to Matthew for pointing it out to me). Stay tuned. Adelle.
|