3. Schema design for simulation projects

A simulation project is not a single dataset; it is a structured web of physical objects, engineering assumptions, solver settings, and execution records. A CAE analyst who runs a modal analysis on a bracket must track which geometry revision was used, which material grade was assigned, where the fixed supports and loads were applied, which solver version and tolerances were chosen, and which run produced the final result set. If the database schema collapses all of this into one wide table, the project becomes impossible to query, compare, or audit. The goal of this chapter is to design a normalized schema that keeps each of these concerns in its own table while preserving the relationships that make cross-run comparison possible.

Core entity model

The schema should be organized around five principal entities: assemblies (the physical product structure), materials (reusable property definitions), boundary conditions (loads and constraints applied to specific regions), solvers (software and version metadata), and run configurations (the parameter set that binds an assembly, material assignment, boundary conditions, and solver into an executable job). A sixth table, runs, records each actual execution of a configuration, because the same configuration may be executed multiple times with different random seeds, mesh refinements, or hardware.

The central design decision is where to place the foreign keys. A run configuration should reference an assembly revision, a solver, and a set of boundary conditions. Material assignments, however, are many-to-many: one assembly part can use one material, and one material can appear in many parts across many assemblies. This calls for a junction table such as part_material_assignment with columns part_id, material_id, and assignment_revision. Boundary conditions similarly attach to specific geometric entities (faces, edges, vertices), so a boundary_condition table should carry a target_entity_id and a target_type discriminator rather than a free-text description.

Why normalization matters for simulation traceability

Consider a bracket assembly with three parts: a base plate, a vertical rib, and a mounting boss. The base plate uses Al 6061-T6, the rib uses the same material, and the boss uses Ti-6Al-4V. If material properties are stored directly in the part table, changing the aluminum yield strength requires updating two rows and risks inconsistency. With a separate material table and a junction table, the property is defined once and referenced twice. More importantly, when a solver run fails because the material model was linear-elastic but the load exceeded yield, the analyst can query which runs used that material grade and which boundary conditions produced the overstress — a query that is only possible when materials, boundary conditions, and runs are separate entities.

Boundary conditions deserve particular attention because they are the most frequently revised part of a simulation project. A fixed support on face F12 with a 500 N downward load on face F27 is a reusable definition. If the load magnitude changes from 500 N to 750 N, the correct operation is to insert a new boundary condition row and a new run configuration, not to overwrite the old row. This append-only pattern preserves the history of what was actually solved. A practical convention is to add a superseded_by self-referencing column on boundary_condition, so the current definition is the row where superseded_by IS NULL.

Run configurations as the binding layer

The run_configuration table is where the schema earns its keep. It should not duplicate material or boundary condition data; it should reference them. A minimal but complete configuration table looks like this:

CREATE TABLE run_configuration (
    config_id       BIGSERIAL PRIMARY KEY,
    assembly_id     BIGINT NOT NULL REFERENCES assembly(assembly_id),
    solver_id       BIGINT NOT NULL REFERENCES solver(solver_id),
    bc_set_id       BIGINT NOT NULL REFERENCES bc_set(bc_set_id),
    mesh_size_mm    NUMERIC(6,3) NOT NULL,
    tolerance       NUMERIC(8,6) NOT NULL DEFAULT 1e-6,
    max_iterations  INTEGER NOT NULL DEFAULT 100,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    notes           TEXT
);

Note that mesh_size_mm and tolerance live on the configuration, not on the solver. The solver table describes the software (name, version, license), while the configuration captures the numerical choices for this particular study. This separation lets an analyst answer a common question: "Did the stress result change because we switched from solver version 2023.1 to 2024.2, or because we refined the mesh from 4 mm to 2 mm?" If both values were stored on the solver row, that comparison would be impossible.

Practical design rules and a worked example

Three rules keep this schema maintainable. First, every table that represents a physical or numerical object should have an immutable primary key and a separate revision or version column; never reuse an ID after a definition changes. Second, geometric targets in boundary conditions should be stored as stable entity identifiers exported from the CAD or meshing tool, not as human-readable labels like "front face," because labels drift between revisions. Third, result data should live outside this schema in a results table or file store keyed by run_id, keeping the project schema lean and fast to query.

As a concrete check, suppose an analyst wants to compare the maximum von Mises stress across three mesh densities for the bracket under a 750 N load. The query joins run to run_configuration, filters on assembly_id = 42 and bc_set_id = 7, orders by mesh_size_mm, and returns run_id, mesh_size_mm, and the peak stress stored in the results table. If the peak stress changes by less than 2% between the 3 mm and 2 mm meshes, the analyst has evidence of mesh convergence; if it changes by 15%, the coarser mesh is inadequate. That judgment depends entirely on the schema keeping mesh size, boundary condition set, and result record cleanly linked — which is the purpose of the design presented here.