3. Entity-relationship modeling of simulation campaigns, models, and result sets.

CAE simulation data is not a flat pile of files. It is a hierarchy of intent: a campaign expresses why a set of analyses exists, a model captures the physical and numerical configuration that was solved, and a result set records the outputs produced by one solver execution against one model. Entity-relationship (ER) modeling forces these distinctions into explicit entities, attributes, and cardinalities before any table is created. Getting this layer right is what prevents the classic failure mode of simulation databases: thousands of result files that cannot be traced back to the geometry revision, material deck, or solver version that produced them.

Why a campaign is an entity, not a folder

A campaign is a bounded investigation with a shared question — for example, "evaluate crashworthiness of the B-pillar across three gauge thicknesses and two materials." It has an owner, a start and end date, a target performance metric, and a set of acceptance criteria. Treating the campaign as a first-class entity lets you attach those attributes once and reference them from every model and result beneath it. If you instead encode the campaign only in directory names or filename prefixes, you lose the ability to query across campaigns ("which campaigns targeted intrusion below 120 mm?") and you duplicate metadata on every file.

The campaign entity typically carries: a surrogate primary key, a human-readable code (e.g. BPILLAR-2024-Q3), a title, an owning engineer, a status (planned, running, complete, archived), and a JSON or text field for acceptance criteria. The status field is worth modeling as a constrained enumeration rather than free text, because downstream queries and dashboards depend on stable values.

Models: the configuration that was actually solved

A model is a versioned, immutable description of what the solver consumed. The critical design decision is that a model must be reproducible: given the model record, an engineer should be able to reconstruct the exact input deck. That means the model entity references, at minimum, a geometry revision, a mesh identifier, a material set, a solver and version, and a set of boundary-condition and load-case definitions. In practice these are separate entities linked to the model, not columns crammed into one wide table, because a single model may share a material set with other models and a geometry revision may be reused across campaigns.

A useful pattern is to separate the model definition (the reusable configuration) from the model instance (the specific snapshot submitted to the solver). The instance records a content hash of the input deck, the submission timestamp, and the compute resource. This distinction matters when a model definition is edited after a run: the instance preserves what was actually executed, while the definition continues to evolve. Without it, editing a material property silently rewrites history for every prior result.

Result sets and their relationship to runs

A result set is the output of one solver execution. It should reference exactly one model instance and carry its own metadata: solver exit status, wall-clock time, number of increments or timesteps, and the location of the output files (ODB, H3D, VTK, or similar). Result sets are the highest-volume entity, so the schema should keep their rows narrow and push large binary payloads to object storage, storing only a URI and a checksum in the database.

The cardinality between model instances and result sets is one-to-many: a single submission may produce a main result set plus restart, checkpoint, or derived sets. Derived result sets — post-processed fields, reduced metrics, extracted curves — should be modeled as a separate entity that references its parent result set, so that provenance is never lost. A common mistake is to store extracted KPIs (peak acceleration, max stress) directly on the result set row; this conflates raw output with analysis and makes it impossible to recompute metrics when the extraction method changes.

Cardinalities that carry real meaning

The relationships below are the ones that most often get flattened incorrectly. Each cardinality encodes a business rule that the database will enforce.

The geometry-to-model relationship deserves emphasis. If geometry revisions are stored as free-text strings on the model, two models that used the same CAD file will not join, and you cannot answer "which results are affected by this design change?" Modeling geometry as its own entity with a revision identifier and a checksum makes that query a simple join.

Reading the model as a diagram

The SVG below shows the core entities and their cardinalities as a crow's-foot style ER diagram. Note the direction of the arrows: they point from the "one" side to the "many" side, and the derived-metric entity hangs off the result set rather than the model instance, which is the structural guarantee that metrics remain traceable to the exact run that produced them.

Translating the ER model into DDL

The diagram maps directly to tables. The key constraints are the foreign keys and the uniqueness rules that prevent duplicate snapshots. A minimal, illustrative schema for the core entities looks like this:

CREATE TABLE campaign (
  campaign_id   BIGSERIAL PRIMARY KEY,
  code          TEXT NOT NULL UNIQUE,
  title         TEXT NOT NULL,
  owner         TEXT NOT NULL,
  status        TEXT NOT NULL CHECK (status IN ('planned','running','complete','archived')),
  criteria      JSONB,
  start_date    DATE,
  end_date      DATE
);

CREATE TABLE model_definition (
  model_def_id    BIGSERIAL PRIMARY KEY,
  campaign_id     BIGINT NOT NULL REFERENCES campaign(campaign_id),
  name            TEXT NOT NULL,
  solver          TEXT NOT NULL,
  solver_version  TEXT NOT NULL,
  geometry_rev_id BIGINT NOT NULL REFERENCES geometry_revision(geometry_rev_id),
  material_set_id BIGINT NOT NULL REFERENCES material_set(material_set_id),
  UNIQUE (campaign_id, name)
);

CREATE TABLE model_instance (
  instance_id      BIGSERIAL PRIMARY KEY,
  model_def_id     BIGINT NOT NULL REFERENCES model_definition(model_def_id),
  input_hash       CHAR(64) NOT NULL,
  submitted_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  compute_resource TEXT,
  UNIQUE (model_def_id, input_hash)
);

CREATE TABLE result_set (
  result_id    BIGSERIAL PRIMARY KEY,
  instance_id  BIGINT NOT NULL REFERENCES model_instance(instance_id),
  exit_status  TEXT NOT NULL,
  wall_time_s  NUMERIC,
  storage_uri  TEXT NOT NULL,
  checksum     CHAR(64) NOT NULL
);

The UNIQUE (model_def_id, input_hash) constraint is the reproducibility guard: resubmitting an identical input deck against the same definition is rejected or explicitly treated as a duplicate, which surfaces accidental reruns early. The UNIQUE (campaign_id, name) constraint keeps model names meaningful within a campaign without forcing global uniqueness.

Common modeling errors and how to detect them

Three mistakes recur in simulation databases. First, storing result file paths as the primary identifier: paths change when storage is reorganized, and the database loses its anchor. Use a surrogate key plus a URI. Second, collapsing model definition and instance into one table: this makes it impossible to distinguish "what we intended" from "what we ran," and any post-hoc edit corrupts provenance. Third, attaching KPIs to the result set row: when the extraction script is corrected, historical values cannot be recomputed without overwriting evidence. Each of these is detectable by a simple query — for example, counting result sets whose instance_id points to a definition modified after the instance's submitted_at reveals in-place edits that should have been new rows.

A well-formed ER model at this layer turns the simulation archive from an unsearchable file share into a queryable record of engineering decisions. The next design step — normalizing material sets and load cases into their own entities — follows naturally once the campaign–model–result spine is stable.