4. Designing result storage schemas

CAE simulation results are not a flat table of numbers. A single run of a transient structural analysis may produce hundreds of time steps, each carrying displacement, stress, and strain tensors at tens of thousands of nodes and elements. A modal or harmonic response analysis replaces the time axis with a frequency axis, and a random-vibration or acoustic study may add mode numbers and phase angles on top. A storage schema that ignores this structure either explodes into redundant rows or collapses into opaque binary blobs that cannot be queried. The goal of this chapter is to design a relational schema that keeps the physical structure of the result set explicit, so that downstream queries — "peak von Mises stress at node 1042 over the last 50 steps," "mode 3 displacement field," "stress tensor at element 88 at 120 Hz" — remain simple, indexable SQL.

Separate the four axes of a result set

Every simulation result can be located by four independent coordinates: which run, which step or frequency, which spatial entity, and which field component. A robust schema models these as separate dimensions rather than cramming them into one wide table. The typical decomposition is:

• Run / case header — one row per solver execution, holding solver name, model revision, units, and coordinate-system convention.
• Step table — one row per time step or frequency, with the physical value (time in seconds, frequency in Hz) and a step index.
• Spatial entity tables — nodes (with coordinates) and elements (with connectivity and type), shared across all steps of a run.
• Field definition table — one row per result field (e.g., U, S, E), declaring its tensor rank and component layout.
• Value tables — the fact tables that join step × entity × field and store the numeric payload.

This star-like structure lets the step axis and the spatial axis grow independently. Adding a new time step inserts one row into the step table and N rows into the value table; it never touches the node or element tables.

Time steps and frequency modes: one axis, two semantics

Time and frequency are both "step" axes, but they carry different semantics and should be distinguished explicitly. A transient analysis has monotonically increasing physical time; a modal analysis has discrete mode numbers with associated natural frequencies; a harmonic sweep has frequency points that may be non-uniform. A single step table with a step_kind discriminator handles all three:

CREATE TABLE result_step (
    step_id      BIGINT PRIMARY KEY,
    run_id       BIGINT NOT NULL REFERENCES run(run_id),
    step_kind    TEXT   NOT NULL CHECK (step_kind IN ('time','frequency','mode')),
    step_index   INTEGER NOT NULL,          -- ordinal within the run
    time_s       DOUBLE PRECISION,          -- populated for step_kind='time'
    freq_hz      DOUBLE PRECISION,          -- populated for step_kind='frequency'
    mode_no      INTEGER,                   -- populated for step_kind='mode'
    UNIQUE (run_id, step_kind, step_index)
);

Keeping step_index as a dense ordinal (0, 1, 2, …) is important: it gives a stable ordering for window functions and range queries even when physical time is non-uniform. The physical value columns are nullable and only populated for the matching kind, which keeps the schema honest about what each row means.

Spatial nodes and elements

Nodes and elements are shared across all steps of a run, so they belong in their own tables. Nodes carry coordinates; elements carry a type and a connectivity list. For a linear tetrahedral mesh, connectivity is four node IDs; for a quadratic hex, twenty. Storing connectivity as a fixed-width array column (PostgreSQL INTEGER[]) or as a child table both work; the array form is compact and fast for whole-element retrieval, while the child table is easier to index for "which elements contain node 1042" queries.

CREATE TABLE node (
    run_id   BIGINT NOT NULL REFERENCES run(run_id),
    node_id  INTEGER NOT NULL,
    x        DOUBLE PRECISION NOT NULL,
    y        DOUBLE PRECISION NOT NULL,
    z        DOUBLE PRECISION NOT NULL,
    PRIMARY KEY (run_id, node_id)
);

CREATE TABLE element (
    run_id       BIGINT NOT NULL REFERENCES run(run_id),
    elem_id      INTEGER NOT NULL,
    elem_type    TEXT NOT NULL,          -- 'C3D4', 'C3D8R', 'S4R', ...
    node_ids     INTEGER[] NOT NULL,     -- connectivity, in solver order
    PRIMARY KEY (run_id, elem_id)
);

Note that node_id and elem_id are scoped by run_id. Solver IDs are only unique within a model, and re-meshing between runs will reuse the same integers for different physical points. Scoping by run prevents silent cross-run collisions.

Scalar, vector, and tensor fields

The field definition table declares the tensor rank and component layout once per field, so the value tables can stay narrow:

CREATE TABLE field_def (
    field_id     INTEGER PRIMARY KEY,
    field_name   TEXT NOT NULL,          -- 'U', 'S', 'E', 'TEMP'
    tensor_rank  SMALLINT NOT NULL,      -- 0 scalar, 1 vector, 2 tensor
    n_components SMALLINT NOT NULL,      -- 1, 3, or 6 (symmetric tensor)
    component_names TEXT[] NOT NULL      -- e.g. {'S11','S22','S33','S12','S13','S23'}
);

For a symmetric stress tensor, six components suffice; for a general tensor, nine. Storing the component names in the field definition lets a query pivot from long to wide form without hard-coding column names. The value table then stores one row per (step, entity, field, component):

CREATE TABLE field_value (
    run_id      BIGINT NOT NULL,
    step_id     BIGINT NOT NULL REFERENCES result_step(step_id),
    entity_kind TEXT   NOT NULL CHECK (entity_kind IN ('node','element')),
    entity_id   INTEGER NOT NULL,
    field_id    INTEGER NOT NULL REFERENCES field_def(field_id),
    component   SMALLINT NOT NULL,       -- 0..n_components-1
    value       DOUBLE PRECISION NOT NULL,
    PRIMARY KEY (run_id, step_id, entity_kind, entity_id, field_id, component)
);

This long-form layout is the workhorse. It is compact, indexable, and uniform across scalar, vector, and tensor fields. A scalar temperature field simply has n_components = 1; a displacement vector has three; a stress tensor has six. The cost is that retrieving a full tensor requires six rows, which is why the primary key is ordered to make that retrieval a single index range scan.

Choosing between long and wide storage

Long form is flexible but pays a join cost per component. For hot fields that are queried as a whole tensor on every step, a wide materialized view or a partitioned wide table can be worth the duplication. A practical compromise is to keep the long-form table as the source of truth and build a wide view for the top three or four fields:

CREATE VIEW stress_wide AS
SELECT v.run_id, v.step_id, v.entity_id,
       MAX(v.value) FILTER (WHERE v.component = 0) AS s11,
       MAX(v.value) FILTER (WHERE v.component = 1) AS s22,
       MAX(v.value) FILTER (WHERE v.component = 2) AS s33,
       MAX(v.value) FILTER (WHERE v.component = 3) AS s12,
       MAX(v.value) FILTER (WHERE v.component = 4) AS s13,
       MAX(v.value) FILTER (WHERE v.component = 5) AS s23
FROM field_value v
JOIN field_def f ON f.field_id = v.field_id
WHERE f.field_name = 'S'
GROUP BY v.run_id, v.step_id, v.entity_id;

The view keeps the storage normalized while giving analysts a familiar wide shape for ad-hoc queries. If the view becomes a bottleneck, it can be materialized and refreshed per run.

Partitioning and indexing for realistic result volumes

A transient run with 50,000 nodes, 200 steps, and three tensor fields produces roughly 50,000 × 200 × 3 × 6 ≈ 180 million value rows. At that scale, partitioning is not optional. Partition field_value by run_id (hash or list) so that dropping a run is a metadata operation, and add a composite index on (run_id, step_id, entity_kind, entity_id) to serve the most common access pattern: "all fields at one entity across steps" or "all entities at one step."

Worked example: querying peak stress over a time window

With the schema above, "peak von Mises stress at node 1042 over the last 50 time steps" becomes a straightforward aggregate. Von Mises is derived from the six stress components, so the query pivots first:

WITH s AS (
  SELECT v.step_id,
         MAX(v.value) FILTER (WHERE v.component = 0) AS s11,
         MAX(v.value) FILTER (WHERE v.component = 1) AS s22,
         MAX(v.value) FILTER (WHERE v.component = 2) AS s33,
         MAX(v.value) FILTER (WHERE v.component = 3) AS s12,
         MAX(v.value) FILTER (WHERE v.component = 4) AS s13,
         MAX(v.value) FILTER (WHERE v.component = 5) AS s23
  FROM field_value v
  JOIN field_def f ON f.field_id = v.field_id
  JOIN result_step st ON st.step_id = v.step_id
  WHERE v.run_id = 7 AND v.entity_kind = 'node' AND v.entity_id = 1042
    AND f.field_name = 'S' AND st.step_kind = 'time'
    AND st.step_index >= (SELECT MAX(step_index) - 49 FROM result_step
                          WHERE run_id = 7 AND step_kind = 'time')
  GROUP BY v.step_id
)
SELECT step_id,
       SQRT(0.5*((s11-s22)^2 + (s22-s33)^2 + (s33-s11)^2)
            + 3*(s12^2 + s13^2 + s23^2)) AS von_mises
FROM s
ORDER BY von_mises DESC
LIMIT 1;

The query is verbose but mechanical, and it scales: the same pattern works for any tensor field by swapping the component filters. If this pattern is common, wrap it in a SQL function von_mises(run_id, entity_id, step_id) so analysts do not re-derive the formula each time.

Schema design checklist

The schema above is deliberately conservative: it favors explicit structure and queryability over raw load speed. For very large runs, the loader can batch-insert into field_value using COPY or multi-row INSERT, and the partitioning strategy can be tuned after the first real run reveals the actual row counts. The key discipline is to keep the four axes — run, step, entity, field — as separate, indexed dimensions, so that every downstream query is a matter of joining them rather than parsing a blob.

The diagram above shows how the four axes converge on the field_value fact table. The run dimension fans out to steps, nodes, and elements; the step and spatial dimensions each contribute one key to the fact table; and the field definition supplies the component layout. Because each dimension is a separate table with its own primary key, adding a new field or a new step kind is a schema extension, not a rewrite of the value storage.