 |
The Business Rules have been drafted.
A Physical Data Model is shown below and
An Access Database is available on request.
DESIGN NOTES :-
The design is very simple.
Unfortunately, writing the SQL to display Hierarchies is not so simple, UNLESS you are using Oracle.
Oracle has provided a 'CONNECT BY' extension to ANSI-standard SQL that handles hierarchies very easily.
A neat demonstration
can be seen which uses a powerful product called Dynamic PL/SQL.
Details for Oracle are shown on our Business Rules page for this Data Model.
However, for SQL Server and DB2 the SQL to retrieve a hierarchy is not so simple.
The best approach is to define an underlying Table with a generic design for hierarchies, and
then use SQL Views to retrieve the data and define the layout for display.
Joe Celko has a discussion in Chapter 26 on
'Trees' in his book 'SQL for Smarties' (5 Stars on Amazon).
|
|
