Where hierarchy table(s) exist which facilitate summation and reporting of primary level data, should these files be counted as logical files?

The only information required to specify a hierarchy is a Parent attribute recorded for each entity. Having established the parent of an entity, the hierarchy can be established simply by following the 'parent trail' up the hierarchy.

Most systems that require hierarchy information for data load, viewing and reporting purposes have set up separate physical tables for recording relationship/hierarchy information. However such tables exists for performance reasons only.

User Requirement

Accounting information is recorded against Org Units, which are hierarchically structured. For example:

The business must be able to report on this information at any level in the Org Unit hierarchy.

Technical Design

It was decided to implement the requirements by linking the tables containing accounting data (ACCOUNT and BUDGET) to the appropriate Org Unit (ORG_UNIT), and creating a separate table (OU_ASSOC) to maintain the Org Unit hierarchy.

The OU_ASSOC hierarchy table stores Org_Unit and Org_Unit_Parent attributes from which the hierarchy can be constructed. For example, in this scenario the OU_ASSOC would look like:

The user is able to select any Org Unit to report; the Org Unit hierarchy is then constructed from the OU_ASSOC table and all accounting information is selected and reported for all Org Units at and below the Org Unit selected.

Files

In this scenario, three logical files may be counted, these being Org_Unit, Account and Budget. The Org_Unit_Parent attribute from the OU_Assoc file is counted as an additional DET on the Org_Unit ILF. The OU_Assoc file itself is not counted.

Transactions

As well as conventional maintenance transactions on the logical files, transactions that maintain the Org Unit hierarchy are also counted as unique External Inputs that reference the Org_Unit ILF. The transactions that permit viewing of the Org Unit hierarchy are counted as External Enquiries.

Hierarchies do not differ from any other Category/Component structures that maintain one to many relationships (e.g. Product_Category and Product) and therefore should be counted in a similar way.

Files

Hierarchy tables that comprise only Entity_Id and Entity_Parent attributes should not be counted as logical files. These files exist for performance reasons only. Logically, the attribute that stores the relationship information (i.e. Entity_Parent) could be stored against the entity.

One ILF should be counted for each unique entity within the hierarchy structure, if the entity attributes are different from other organisational entity attributes. For example State, Region, District, Branch, etc. If there is only one recursive entity, for example, Organisation Unit, count only one ILF. If the entity participates in only one relationship, count one additional DET. If the entity participates in multiple relationships, count a separate RET for the subgroup of data elements that define the multiple relationships the entity participates in. This RET comprises two DETs. They are Relationship_Type, Link_Foreign_Key)

Transactions

The transactions that maintain the relationships are counted as unique External Inputs and the transactions that permit the viewing of the hierarchy structure are counted as External Enquiries.