A test database commonly stores one record for an attempt and several child records for its measured steps. If those relationships are weak, a step can become orphaned or attach to the wrong retry. Design the identity model around the attempt, not merely around the board serial number.
The SQLite foreign-key documentation describes constraints that require child references to correspond to parent keys and explains enforcement configuration. Check that the feature is actually enabled in the deployed connection; declaring a relationship in a diagram does not enforce it in stored data.
Define the parent identity
Give each attempt a distinct identifier and attach its board identity, recipe revision, station, and start state. Then reference that attempt from step records. A board can legitimately have several attempts, so using the serial as the only parent key can mix initial tests and retests.
Define step identity within an attempt. A repeated measurement step may need an iteration or acquisition identifier in addition to its name. Otherwise a later reading can overwrite an earlier one while appearing to update the same expected record.
Check relationship behavior
| Operation | Expected outcome to define |
|---|---|
| Insert step without parent | Reject or retain through an explicit staging process |
| Repeat a step identifier | Detect duplicate or represent a new iteration |
| Delete an attempt | Apply the intended retention policy to its children |
| Import historical results | Preserve original relationships and identity scope |
Choose deletion and update behavior deliberately. Automatic cascading may be useful in a temporary staging database but inappropriate for preserving released test history. The database behavior should match the record-retention design rather than a convenient default.
Validate the deployed writer
- Check the actual schema and connection configuration.
- Insert a valid parent and its steps.
- Exercise an invalid parent reference.
- Test retry and repeated-step scenarios.
- Audit existing data for orphaned or ambiguous records.
Include interrupted writes in the validation. An attempt can be present but incomplete, and that state should remain visible. Do not manufacture a completed parent status simply because some child rows exist, or assume that every declared step must already have produced a measurement.
Keep structural integrity separate from semantic correctness. A foreign key can prove that a referenced attempt exists, but it cannot prove the operator scanned the correct board. Combine enforced relationships with the station’s identity checks and clear event records.
The final database should let a reviewer follow any measurement back to one board, one attempt, and one recipe context without relying on timestamp proximity or spreadsheet row position.
Move from reading to a build-ready project
Use the fabrication and assembly checklists to prepare drawings, production files and test requirements.
Open the DFM checklist