2. Relational model fundamentals for simulation data

CAE simulation campaigns generate data that is deceptively simple at first glance: a mesh, a set of boundary conditions, a solver run, and a result field. The moment a project contains more than one load case, more than one design variant, or more than one solver version, that simplicity collapses. The relational model gives us a disciplined way to keep these artifacts separate, link them correctly, and query across them without ambiguity. This chapter establishes the vocabulary and the structural rules that every later schema decision depends on.

From solver output to relations

A relation is a set of tuples sharing a common, named set of attributes. In practice, each relation becomes a table, each tuple a row, and each attribute a column with a declared data type. The critical property is that a relation has no inherent row order and no duplicate rows — identity comes from data values, not from position. This matters for simulation data because solver output files are ordered by node ID or element ID, but that ordering is an artifact of the writer, not a semantic property. Once loaded into a table, ordering must be re-established explicitly with ORDER BY whenever it is meaningful.

Consider a typical linear static run. The natural decomposition is not one wide table but several narrow ones:

• simulation — one row per solver execution: solver name, version, start time, status, input deck path.
• load_case — one row per load case within a simulation: case label, gravity vector, applied pressure, constraint set.
• node — one row per mesh node: coordinates in the model coordinate system.
• element — one row per finite element: element type, connectivity to nodes.
• result_displacement — one row per (load case, node): three displacement components.
• result_stress — one row per (load case, element, integration point): stress tensor components.

Each table stores one kind of fact at one granularity. This is the first design rule: a table should answer exactly one question about one entity type. Mixing mesh geometry and result values in a single table forces every geometry row to repeat result columns or carry nulls, and it makes re-meshing a destructive operation on results.

Keys: establishing identity

A candidate key is a minimal set of attributes that uniquely identifies each tuple. One candidate key is chosen as the primary key; the rest remain alternate keys enforced by unique constraints. For simulation data, two key strategies are common and they serve different purposes.

Natural keys come from the domain. A node is naturally identified by (simulation_id, node_id) because node numbering is only unique within a given mesh. An element is identified by (simulation_id, element_id). A result value is identified by (load_case_id, node_id) for nodal results. Natural keys are meaningful and make joins self-documenting, but they are fragile: if a mesh is renumbered, every referencing row must change.

Surrogate keys are system-generated integers or UUIDs with no domain meaning. They are stable under renumbering and compact for indexing, but they carry no information and require an additional unique constraint to preserve the natural identity. A pragmatic pattern is to use a surrogate primary key and declare the natural key as a UNIQUE constraint, so both stability and meaning are preserved.

Foreign keys and referential integrity

A foreign key is an attribute (or attribute set) in one table whose values must match a candidate key in another table. It encodes the relationship and lets the database reject orphaned rows. In the schema above, result_displacement.load_case_id references load_case.load_case_id, and load_case.simulation_id references simulation.simulation_id. The chain from a displacement value back to the solver version that produced it is then guaranteed to be traversable.

Referential actions deserve deliberate choice. ON DELETE CASCADE from simulation to load_case is appropriate: deleting a simulation should remove its cases and results. But cascading from node to result_displacement is dangerous — a partial mesh edit could silently delete result rows. In that case ON DELETE RESTRICT forces the operator to acknowledge the dependency. Simulation archives are append-mostly, so restrictive deletes are usually the safer default.

Relationship cardinality

The relational model expresses three cardinalities, and simulation data uses all of them:

The many-to-many case is worth spelling out because it appears constantly. A material definition (density, elastic modulus, Poisson ratio) is reused by many simulations, and a simulation references many materials. The junction table simulation_material(simulation_id, material_id, assignment_role) resolves this, where assignment_role distinguishes, for example, 'structural' from 'contact'. The junction table's primary key is the pair of foreign keys, and any additional attribute belongs to the relationship itself, not to either entity.

Normalization as a correctness tool

Normalization is usually taught as a storage optimization; for simulation data it is primarily a correctness discipline. First normal form requires atomic attribute values — no comma-separated node lists in a single cell. Second normal form requires that non-key attributes depend on the whole key, which prevents storing a node's coordinates on a result row keyed by (load_case_id, node_id). Third normal form removes transitive dependencies, such as storing the solver version on every result row when it is determined by simulation_id alone.

The practical test is a simple question: if this fact changes, how many rows must be updated? If a solver version upgrade requires touching ten million result rows, the schema is denormalized in a way that will eventually produce inconsistent data. Denormalization is legitimate for query performance, but it should be a conscious, documented decision with a refresh mechanism — not an accident of convenience.

Working through a query that depends on the model

The value of a correct relational structure becomes visible in queries. Suppose we need the maximum vertical displacement per load case for a specific solver version, together with the node coordinates where it occurs. With the schema above, the query is a straightforward join chain:

SELECT lc.case_label,
       n.node_id,
       n.x, n.y, n.z,
       d.uz
FROM   result_displacement d
JOIN   load_case lc ON lc.load_case_id = d.load_case_id
JOIN   simulation s ON s.simulation_id = lc.simulation_id
JOIN   node n       ON n.simulation_id = lc.simulation_id
                   AND n.node_id      = d.node_id
WHERE  s.solver_version = '2024.2'
  AND  d.uz = (SELECT MAX(d2.uz)
               FROM   result_displacement d2
               WHERE  d2.load_case_id = d.load_case_id);

Two details in this query are direct consequences of the model. First, the join from node requires both simulation_id and node_id, because node identity is scoped to the mesh — a single-column join on node_id would silently match nodes from other simulations. Second, the correlated subquery is safe only because load_case_id is a foreign key that guarantees every result row belongs to exactly one case. If that constraint were missing, orphaned result rows would be excluded from the maximum and the reported peak would be wrong without any error being raised.

Design checklist before loading any results

Before a single solver output file is ingested, confirm the following. Every table has a declared primary key, and every natural identity is protected by a unique constraint. Every relationship is enforced by a foreign key with an explicitly chosen delete action. Result tables carry the full composite key that scopes them to one load case and one mesh entity. Coordinate systems are recorded as an attribute rather than assumed, so that displacement components remain interpretable when models mix global and local frames. With these rules in place, the schema is not merely a container for numbers — it is a guarantee that every number can be traced to the simulation, load case, and mesh entity that produced it.