Database Answers Header Steps in Peacocks Pond, Florida, courtesy Robert Smith, Australia
Home Ask a Question FAQs Data Models Search Site Map  
  • A (Dezign) Data Model is shown below and An Access Database is available.

    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.

    Dezign Data Model for Hierachies
  • Barry Williams
    16th. November 2002
    Principal Consultant
    Database Answers

    [ Home Page | Ask Us a Question | Email Us | Site Map ]

    © 2002