Article Preview
Buy Now
COLUMN
Who's the man?
Dealing with hierarchies using the Adjacency Model
Issue: 11.5 (September/October 2013)
Author: Craig Boyd
Author Bio: Craig Boyd is currently an Oracle DBA for a well-known national retailer. But in his 18 years of IT experience, he has been everything from a PC Technician to iSeries System Administrator to iSeries Programmer, a Sr. Technical Lead and a Data Modeler. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 16,523
Starting Page Number: 81
Article Number: 11513
Related Link(s): None
Excerpt of article text...
There are few things in this world that are really and truly simple. This quickly becomes apparent when you are trying to model something in the real world. A wise woman by the name of Karen Lopez once observed that "models do not create complexity, but rather they reflect it." The more complex the object being modeled, the more complex the model. At first this seems obvious, but you would be surprised at how some people will thoughtlessly comment about how simple something is until they actually try to tackle it. One such topic that most people think at first blush is simple are hierarchies, but once you start digging into the details it quickly becomes obvious that there is more to this than meets the eye. In this column we are going to spend a little time talking about hierarchies and how to work with them in a database. Hopefully, by the end of this article you will have a much greater appreciation and understanding for how to deal with hierarchies.
So what is a hierarchy? Here are a couple definitions from Webster's: "a body of persons in authority" and "a graded or ranked series." There are other definitions as well, but those are less relevant to this discussion. The first definition speaks to what most of us think about when we hear the word hierarchy: an organizational chart. Organizations need hierarchies in order to organize people and show where they fit in. And while this may be what we think of most commonly in hierarchies there are other types of hierarchies as well that have completely different rules. For example, the animal and plant kingdoms are arranged in hierarchies. Family genealogies are hierarchies. Products and parts can be in hierarchies. With a bit more time I am sure we could come up with many more.
For the sake of simplicity I will keep this discussion limited to organizational hierarchies since that is what most of us have to deal with in one form or another.
There are two or three standard approaches to designing hierarchies in a database. We are going to look at the most common one which is called the "Adjacency Model." In the adjacency model each person has a manager until you get to the top of the chain in which case the top manager has no value in the manager column. Each individual has their boss associated with them, which is the immediate level above them or adjacent to them, hence the name.
So, for example:
...End of Excerpt. Please purchase the magazine to read the full article.