Hierarchy data on DB layer

Simple ways

ParentId-to-ChildId

1. define primary key in table
2. define ParentRef column that has the same data type as primary key has and allowed NULL;

Pluses:

• easy to implement
• good structure where data accessed level by level;

Minuses:

• very complex queries for data extracting; implementation of query "extract node and all it childs" become very complex;

Separator

1. define separator that can not be used by user

Pluses:

• easy to implement
• easy to extract node and it childs

Minuses:

• some times is very hard to define seprator
• growing of Name column value can become a problem if required deep hierarchy;

Combination

1. define primary key in table
2. define ParentRef column that has the same data type as primary key has and allowed NULL;
3. define special index column varchar()

Pluses:

• combination of performance and DB space usage
• easy to extract data in any suitable way

Minuses:

• required DB trigger on table for update and delete operations

Example

Separator is "/" char. Used 4 (four) chars for index key storage;