A test called fifo_stress fails twice with the same seed. Did the importer duplicate a row, or did an engineer retry the test? A name and seed alone cannot answer. First define the grain: what one row represents. In this section, one runs row means one run attempt, even if its test name, seed, and build match another attempt. A retry receives a new run_id. This definition prevents a cleanup script from deleting valid evidence simply because two rows look similar.
A primary key identifies a row inside a table. A foreign key links a row to a parent in another table. For our fictional regression, builds contains one row per recorded build and runs contains many attempts per build. The build_id on runs points to builds.build_id. Keep descriptive labels for humans, but join on stable keys with known scope. If two projects can both issue build B1, include the project identity in the key or allocate globally distinct identifiers.
Table | Key | Records and meaning |
|---|---|---|
builds | build_id | B1 uses revision rev_a; B2 uses rev_b; B3 uses rev_c. |
runs | run_id | R1, R2, R3 belong to B1; R4, R5 belong to B2; B3 has no attempts. |
failure_tags | run_id plus tag | R2 has timeout and reset; R3 has timeout; R5 has compile. |
R1 and R4 can both be reset_smoke, seed 11, while remaining different attempts on different builds. If R2 is retried on B1, its retry also needs a distinct run_id and optionally a retry_of reference. Do not overwrite the first failure with the retry result. A last-result dashboard may intentionally select one attempt, but its selection rule belongs in the query and report caption.
The failure_tags table illustrates a second relationship: one failed run can have several labels. Joining runs to tags produces one row per matching tag, not one row per run. In this example there are three tagged attempts and four tag rows. A count after that join can therefore overstate the number of affected attempts. Identify this change in grain before treating a join as a harmless way to add columns.
builds to runs is one-to-many. Start from builds and LEFT JOIN runs using build_id. Count runs.run_id, a non-null key on real child rows. B1 has 3 attempts, B2 has 2, and B3 has 0. COUNT(*) would count the preserved empty-side row for B3 as 1.
Write down three checks before import: primary keys must be unique, every referenced build must exist, and status must be in the agreed vocabulary. A foreign key can catch an unknown build_id when enforcement is enabled. It cannot tell you that an importer attached a run to the wrong existing build. Preserve the source manifest or launch record to make that error reviewable.
For STA and PD, use the same reasoning with a different grain. One timing-summary row might mean build, mode, corner, and analysis stage together. One physical-design metric might also depend on floorplan revision and tool setup. Omitting those dimensions can merge unrelated measurements into an apparently clean table. Put the unit in the column name or a controlled metadata field, and document whether a count represents paths, endpoints, cells, or violations.
Keys preserve relationships; they do not establish causality or verify engineering correctness. Agree on deletion and retention with the data owner before production use. This course only reads approved source records; its later lab creates disposable synthetic tables in memory. SQLite foreign-key enforcement must be enabled per connection before the lab inserts records. Reference: SQLite foreign-key documentation, consulted 2026-10-18.