DESIGN NOTES :-
The design is very simple. Unfortunately, the SQL to retrieve a hierarchy is not so simple.
The best approach is to use SQL Views to define the layout that you want to display.
Joe Celko has a comprehensive discussion in Chapter 26 on 'Trees' in his book 'SQL for Smarties' (5 Stars on Amazon).
Oracle has provided an extension to SQL called CONNECT BY, and has also added appropriate faciltities to 11g for Modelling a Ragged Product Hierarchy and here's an interesting Blog.
Microsoft has added a Hierarchy ID to SQL Server 2008 which is definitely worth checking out.
Here is also a version showing 'dotted line' relationships and partial Hierarchy Strings.