4. Schema design patterns for hierarchical simulation assemblies and subcases.

CAE models are rarely flat. A full-vehicle crash model contains a body-in-white, closures, powertrain, and dummy subsystems; each subsystem is itself an assembly of parts, and each part may carry multiple result sets from different load cases. A solver deck mirrors this structure through include files and assembly instances, but a relational database does not inherit that hierarchy for free. The schema designer must decide how to represent the parent–child relationships, how to attach results at the correct level, and how to keep queries fast when the tree is deep. This section presents the three dominant patterns — adjacency list, materialized path, and closure table — and shows how to combine them with a subcase dimension so that assembly hierarchy and load-case hierarchy do not collapse into one ambiguous tree.

Separating the assembly tree from the subcase tree

The first design decision is to recognize that two independent hierarchies coexist in simulation data. The assembly hierarchy answers "which part belongs to which subsystem," and it is structural: a node has exactly one parent in a given model version. The subcase hierarchy answers "which load case was derived from which," and it is analytical: a base static case may spawn dozens of linear perturbation subcases, each with its own boundary conditions and result sets. Conflating them into a single self-referencing table produces rows whose meaning depends on a type flag, and every query must then filter on that flag. Keep them as two tables that share a common model_version_id foreign key.

CREATE TABLE assembly_node (
    node_id          BIGINT PRIMARY KEY,
    model_version_id BIGINT NOT NULL REFERENCES model_version(model_version_id),
    parent_node_id   BIGINT NULL REFERENCES assembly_node(node_id),
    node_name        TEXT   NOT NULL,
    node_kind        TEXT   NOT NULL CHECK (node_kind IN ('assembly','part','instance')),
    cad_ref          TEXT,
    UNIQUE (model_version_id, parent_node_id, node_name)
);

CREATE TABLE subcase (
    subcase_id       BIGINT PRIMARY KEY,
    model_version_id BIGINT NOT NULL REFERENCES model_version(model_version_id),
    parent_subcase_id BIGINT NULL REFERENCES subcase(subcase_id),
    subcase_name     TEXT   NOT NULL,
    analysis_type    TEXT   NOT NULL,   -- 'static','modal','buckling', ...
    step_index       INT    NOT NULL DEFAULT 1
);

Note the UNIQUE (model_version_id, parent_node_id, node_name) constraint. It enforces that sibling names are unique within a version, which matches how solver include files resolve duplicate part names, and it also gives the query planner a useful composite index for child lookups. The parent_node_id is nullable so that the root assembly can be represented without a sentinel row.

Pattern 1: adjacency list as the write-optimized baseline

The schema above is an adjacency list: each row stores only its immediate parent. It is the natural representation because it mirrors how meshing and assembly tools export data, and inserts are cheap — a new part is one row. The cost appears on reads. To retrieve all descendants of a subsystem, you need a recursive CTE:

WITH RECURSIVE subtree AS (
    SELECT node_id, parent_node_id, node_name, 0 AS depth
    FROM   assembly_node
    WHERE  node_id = :root_id
    UNION ALL
    SELECT c.node_id, c.parent_node_id, c.node_name, s.depth + 1
    FROM   assembly_node c
    JOIN   subtree s ON c.parent_node_id = s.node_id
)
SELECT * FROM subtree ORDER BY depth, node_name;

On PostgreSQL this is efficient for trees up to a few thousand nodes, and it is the right default when the model is rebuilt frequently and read patterns are ad hoc. The failure mode to watch for is a cycle introduced by a bad import: a part accidentally parented to its own descendant. The recursive CTE will loop until it exhausts memory. Guard against this with a depth cap (WHERE s.depth < 64) and a separate validation query that checks for cycles before committing a model version.

Pattern 2: materialized path for read-heavy reporting

When the dominant workload is "give me every result on every part under the front crash subsystem," the recursive walk becomes the bottleneck. A materialized path stores the full ancestry as a text column, for example /root/body/front_end/rail_left. Descendant queries then become a single indexed prefix scan:

ALTER TABLE assembly_node ADD COLUMN path TEXT NOT NULL;
CREATE INDEX assembly_node_path_idx ON assembly_node (model_version_id, path text_pattern_ops);

SELECT n.*
FROM   assembly_node n
WHERE  n.model_version_id = :mv
  AND  n.path LIKE :subsystem_path || '/%';

The trade-off is maintenance. Moving a subsystem requires rewriting the path of every descendant, which is a single UPDATE ... WHERE path LIKE old || '/%' but touches many rows. Path length also grows with depth; a 12-level tree with 20-character names approaches 250 characters, which is fine for TEXT but wasteful in indexes. Use this pattern when the assembly tree is stable across many result imports and reporting latency matters more than occasional restructuring.

Pattern 3: closure table for arbitrary ancestor queries

Some analyses need "which subcases affect this part" or "which parts are shared between two subsystems." Both are ancestor/descendant questions in disguise. A closure table stores one row per ancestor–descendant pair, including the zero-length self pair:

CREATE TABLE assembly_closure (
    ancestor_id   BIGINT NOT NULL REFERENCES assembly_node(node_id),
    descendant_id BIGINT NOT NULL REFERENCES assembly_node(node_id),
    depth         INT    NOT NULL,
    PRIMARY KEY (ancestor_id, descendant_id)
);

With this table, "all parts under subsystem X" is a plain join with no recursion, and "is part P under subsystem X" is a single primary-key probe. The cost is storage: a balanced tree of n nodes stores O(n log n) pairs, and a degenerate chain stores O(n²). For a 50,000-node model this is usually acceptable; for a 500,000-node model it is not. Populate the closure table inside the same transaction that inserts the assembly nodes, using a trigger or a post-import procedure, so it can never drift from the adjacency list.

Attaching results at the correct level

The result table is where hierarchy mistakes become expensive. A result set is identified by a triple: the assembly node it belongs to, the subcase it was computed under, and the step or frame index. Store all three as foreign keys and never denormalize the node path into the result row:

CREATE TABLE result_set (
    result_set_id  BIGINT PRIMARY KEY,
    node_id        BIGINT NOT NULL REFERENCES assembly_node(node_id),
    subcase_id     BIGINT NOT NULL REFERENCES subcase(subcase_id),
    step_index     INT    NOT NULL,
    quantity       TEXT   NOT NULL,   -- 'von_mises','displacement_mag', ...
    max_value      DOUBLE PRECISION,
    min_value      DOUBLE PRECISION,
    file_uri       TEXT   NOT NULL,
    UNIQUE (node_id, subcase_id, step_index, quantity)
);

Two rules keep this table honest. First, a result may only be attached to a leaf part or to an assembly that actually carries a result in the solver output — do not propagate a child's result up to its parent by inserting duplicate rows. Second, the subcase_id must belong to the same model_version_id as the node_id. Enforce this with a composite foreign key or a check trigger, because a mismatched pair silently produces results that look valid but belong to a different model revision.

Choosing a pattern in practice

A pragmatic default for CAE result databases is to keep the adjacency list as the source of truth and add a closure table as a derived index, rebuilt after each model import. This gives fast ancestor queries without the maintenance burden of materialized paths, and the adjacency list remains the authoritative structure that import tooling writes to. If the model is small (under roughly 10,000 nodes) and read latency is not critical, the adjacency list alone with recursive CTEs is simpler and entirely adequate.

Verifying the hierarchy after import

After loading a model version, run three checks before accepting results. First, confirm every non-root node has a parent in the same version: SELECT count(*) FROM assembly_node WHERE parent_node_id IS NULL AND node_id <> :root_id must return zero. Second, confirm no cycles exist by walking the closure table and asserting that no row has ancestor_id = descendant_id AND depth > 0. Third, confirm every result row's node and subcase share a model version. If any check fails, reject the import transaction rather than patching rows afterward — a partially loaded hierarchy is harder to diagnose than a clean failure.

With the hierarchy and subcase structures separated and the result table keyed on both, the schema can answer the two questions that dominate CAE reporting — "what happened under this subsystem" and "what happened in this load case" — without ambiguity, and it can do so with either a recursive walk or an indexed join depending on which pattern the workload favors.