More on Trees

A common table design to store hierarchical data structure – is a parent field, with reference to Primary Key, like in the picture below:



There is one more interesting way to store hierarchy – SQL Trees. You can look at Joe Celko articles to find out what it is:

A Look at SQL Trees
SQL Lessons
Nontraditional Databases
When Good Data Goes Bad

Later edition of this article - Trees in SQL, and code samples to it.

Few more articles on SQL Trees:
Storing Hierarchical Data in a Database
Trees in SQL: Nested Sets and Materialized Path
Trees in SQL databases

Michel Walsh, Access MVP, made a sample database in Access.

There are also Russian translations of these articles:
Древовидные структуры в SQL
Деревья в поддерживающих стандарты SQL базах данных

Personally – I never used such structure in my projects. Once I decided to use it, I’ve made a table structure, start building triggers to update data in new fields to correspond old, parent field, approach – the idea was to have both structures work together. But then project suddenly stopped. This was TehnoPro, a CAD system.