Calculating Hierarchical Roll-ups: The Adjacency List Model

I've worked on a couple of projects where the requirements involved calculating a part of an organization chart. There are a number of ways to do this with standard SQL, with the help of database-specific solutions like "CONNECT BY" in Oracle or recursive queries in SQL Server 2005, but in general I think the adjacency list model is a good approach.

For example, take a look at this organizational structure for this
fictional 6 person software start-up

CEO: Joseé Roarke
---CIO: Maria Gandalfino
------Director: Product Development Alex Cohen
---------Developer: Peter Chin
---------Developer: Jake Murphy
---------Developer: Catherine McLaren

Here's what the data would look like in a table in a database:

EmployeeID	Title	Name	BossID
2052	CEO	Joseé Roarke	NULL
2053	CIO	Maria Gandalfino	2052
2054	Director	Alex Cohen	2053
2055	Developer	Peter Chin	2054
2056	Developer	Jake Murphy	2054
2057	Developer	Catherine McLaren	2054

In the adjacency list model, the data is transformed so that the
parent and child (boss and employee) data is stored in the same row.

Boss's Boss	Boss	Employee
Joseé Roarke	Maria Gandalfino	Alex Cohen
Maria Gandalfino	Alex Cohen	Peter Chin
Maria Gandalfino	Alex Cohen	Jake Murphy
Maria Gandalfino	Alex Cohen	Catherine McLaren

This way, with a bit of SQL, any structure in a hierarchy can be
calculated. This can be combined with other parameters like date
ranges, geographical regions to create dynamic reports.