Do not force variable depth into fixed columns

Fixed level1-level7 columns create nulls and wide rewrites when one group has region-store while another has business group-company-city-store.

A parent-child model plus closure table rolls up from any node without requiring uniform depth.

Use stable surrogate node keys

Keep business ID, type, name, and effective period behind node_key. Names, codes, and parents change, and duplicate names can exist.

Model root, unknown, and inactive nodes explicitly so historical facts retain references.

Effective-date hierarchy edges

Store child, parent, relationship type, version, and valid interval. Reorganization closes an edge and creates another.

Legal, managerial, and product hierarchies use separate hierarchy IDs. Multi-parent permission is a domain decision.

Generate closure paths

Closure stores hierarchy, ancestor, descendant, depth, and version, including self at depth zero. Rebuild affected paths atomically after movement.

Edges remain authoritative; closure must be reproducible and never manually edited.

Handle skipped levels honestly

A store may attach directly to region while another passes through city. Render paths by node type without copying a parent into missing levels.

A fixed-level projection may support a report but must declare collapse rules and avoid duplicate counts.

Govern multiple parents and weights

Matrix teams can belong to product and region. Multiple parents within one rollup duplicate facts unless a primary parent or versioned allocation bridge is defined.

Weights conserve finance totals. Repeated display is not the same as cost allocation.

Separate current and event-time hierarchy

History under today’s organization differs from history under the organization at transaction time. Provide explicit current and as_was dimensions.

A default current join silently rewrites trends after reorganization.

Prevent cycles, orphans, and authorization expansion

Validate that a child cannot be its ancestor, parents exist, types are allowed, and effective periods do not overlap. Quarantine orphan and duplicate paths.

Because authorization can inherit down hierarchy, use approved versions and explicit blocks; a report filter is not access control.

Accept with structural and historical tests

Test one node, deep chain, skipped level, move, split, merge, multiple parents, cycle, late change, and deactivation.

Require acyclic reachable roots, reproducible closure, conserved finance rollups, correct current/as_was views, and performance on deep and wide trees.

BuildTable.ai boundary

Pilot one hierarchy, freeze node types and multi-parent policy, load edges, generate closure, and replay reorganizations.

BuildTable.ai may be evaluated for parent-child, closure, and semantic result modeling. Automatic maintenance, complete cycle detection, orchestration, and hierarchy authorization require verification.

How can one report roll up organizations that have three levels and seven levels?