2. Relational model fundamentals for simulation data

CAE simulation output is deceptively simple when viewed as a single file: one model, one load case, one result set. In practice, an engineering program accumulates hundreds of models, each with dozens of load cases, each producing multiple result sets across solver versions and mesh revisions. The relational model gives us a disciplined way to store this information without duplication, ambiguity, or silent inconsistency. This section establishes the core concepts — entities, relationships, keys, and normalization — and applies them directly to the three dominant object types in simulation data management: models, load cases, and result sets.

Entities: What Deserves Its Own Table

An entity is a distinguishable object about which we store data. The first design decision is always the same: what is the grain of each entity? For simulation data, three entities recur in almost every schema:

• Model — a specific geometry/mesh configuration, identified by a model name, revision, and mesh checksum. A model is not a file; it is the engineering artifact the file represents.
• Load case — a boundary-condition and loading definition applied to a model. Load cases are reusable: the same "brake pressure 8 MPa" case may be applied to several model revisions.
• Result set — the output of one solver run: a load case evaluated on a model with a particular solver version and settings, producing fields such as displacement, stress, and strain energy.

Each entity gets a primary key. Surrogate integer or UUID keys are preferable to natural keys because model names get renamed and load-case labels get reused. A natural key such as (model_name, revision) looks convenient until someone renames a model mid-project and every foreign reference must cascade. Use a surrogate model_id and enforce uniqueness on the natural attributes with a separate constraint.

CREATE TABLE model (
    model_id      INTEGER PRIMARY KEY,
    model_name    TEXT    NOT NULL,
    revision      INTEGER NOT NULL,
    mesh_checksum TEXT    NOT NULL,
    UNIQUE (model_name, revision)
);

CREATE TABLE load_case (
    load_case_id  INTEGER PRIMARY KEY,
    case_label    TEXT    NOT NULL,
    case_type     TEXT    NOT NULL,   -- e.g. 'static', 'modal', 'thermal'
    magnitude_si  REAL,               -- canonical SI value
    UNIQUE (case_label, case_type)
);

CREATE TABLE result_set (
    result_set_id INTEGER PRIMARY KEY,
    model_id      INTEGER NOT NULL REFERENCES model(model_id),
    load_case_id  INTEGER NOT NULL REFERENCES load_case(load_case_id),
    solver_version TEXT   NOT NULL,
    run_timestamp  TEXT   NOT NULL,
    status         TEXT   NOT NULL    -- 'complete', 'failed', 'partial'
);

Notice that result_set carries no field values itself. It is the association between a model and a load case, plus run metadata. The actual numbers live in a child table keyed by result_set_id and a field identifier. This separation is the single most important structural decision in simulation schemas: it keeps the run record small and lets result payloads be loaded, indexed, or archived independently.

Relationships and Cardinality

The relationships here are not arbitrary. A model has many result sets (one-to-many). A load case has many result sets (one-to-many). A result set belongs to exactly one model and one load case. The many-to-many relationship between models and load cases is resolved by the result-set table — this is the classic associative-entity pattern, and it is exactly right here because the association itself carries attributes (solver version, timestamp, status).

Cardinality mistakes are common and costly. If you model load cases as columns on the model table (case_1, case_2, …), you have committed to a fixed maximum and made queries like "find all models evaluated under thermal loading" painful. If you model result fields as columns (max_stress, max_disp), every new output quantity requires a schema migration. Both are symptoms of the same error: confusing a value with an attribute.

Normalization Applied to Simulation Data

Normalization removes redundancy and the update anomalies that follow from it. The three normal forms that matter most in practice:

Consider a denormalized result table that stores, per node, the model name, load-case label, solver version, and von Mises stress. For a 2-million-node mesh, the model name is repeated 2 million times. Renaming the model means updating 2 million rows, and a partial update leaves the table internally inconsistent. After normalization, the name exists once in model, and the node-level table holds only (result_set_id, node_id, value).

Where Deliberate Denormalization Is Justified

Normalization is a design default, not a dogma. Simulation result payloads are read-heavy and enormous; joining a 50-million-row field table to a metadata table on every query is wasteful. Two controlled exceptions are common:

• Materialized summary columns on result_set — max_von_mises, max_displacement, min_safety_factor — computed once at ingest. These are derived, documented as such, and refreshed when the payload changes.
• Partitioned field tables keyed by result_set_id, so that a query for one run touches one partition rather than scanning the whole dataset.

The discipline is that denormalization must be explicit and derived. If a value can be recomputed from normalized data, it is a cache; if it cannot, it is a genuine attribute and belongs in the normalized schema.

Putting It Together: A Query That Exercises the Model

A typical engineering question — "which completed static runs on model revision 3 exceeded 250 MPa?" — becomes a clean join across the normalized structure:

SELECT m.model_name, m.revision, lc.case_label,
       rs.solver_version, rs.max_von_mises
FROM   result_set rs
JOIN   model      m  ON m.model_id     = rs.model_id
JOIN   load_case  lc ON lc.load_case_id = rs.load_case_id
WHERE  m.model_name = 'bracket'
  AND  m.revision   = 3
  AND  lc.case_type = 'static'
  AND  rs.status    = 'complete'
  AND  rs.max_von_mises > 250.0
ORDER BY rs.max_von_mises DESC;

Every predicate maps to a single entity, and no fact is stored twice. If the answer looks wrong, the failure is localized: check status for incomplete runs, check units on max_von_mises (store SI consistently — pascals, not megapascals, in the base column), and verify that the load case was not redefined after the run. Those three checks resolve the majority of "missing result" reports in practice.

The diagram makes the structure explicit: result_set is the associative entity that resolves the many-to-many relationship between models and load cases, and result_field hangs beneath it at node granularity. Cardinalities are one-to-many throughout, and every foreign key points toward the entity that owns the referenced fact. With this skeleton in place, adding a new solver, a new output quantity, or a new load-case type requires inserting rows — not altering the schema — which is precisely the property that keeps a simulation database maintainable over a multi-year program.