Subject: Reporting from the Database for Organisations,Staff and Changes

Our Data Model is very flexible.

In a Data Model, hierarchies can be easily implemented using a self-referencing relationship (also called recursive, reflexive or Rabits-Ears).

However, they present a challenge to ANSI SQL, which is why two major vendors have extended SQL to make it easy.

Retrieving data from Hierarchical Tables in a Database can be done using Microsoft's Hierarchy for SQL Server or by Oracle's CONNECT BY,
which is described very well by Phil Greenspun who also discusses some PL/SQL work-arounds, for problems with CONNECT BY, such as
"JOIN doesn't work with CONNECT BY", which is which seems like an alternative approach to Kimball's Bridge Table solution which he
describes on pages 162 to 167 of the Second Edition of his Data Warehouse Toolkit book

Using the "Date From"  field as part of the Primary Key in the Staff Assignments Entity is a
very elegant design, partly because it uses a composite key which 'tells the story' of how this Entity is related to the other Entities.





 © DataBase Answers Ltd. 2014