Design an import order for related records

A dependable import separates valid related records from references that cannot yet be resolved. Stage the data, validate the references and publish parents before children, preserving a report of everything held back.

Executed synthetic walkthrough: the report was run in the actual OriginChainDB console, exported as CSV and validated by the local application adapter. Three reviewed light-mode screenshots are included.

Stage courses + enrollments. Validate references first. Parents → ready children. Hold E3; preserve the reason

Conceptual illustration of the synthetic application workflow. Light PNG · Dark PNG. This is not a product screenshot.

The data contract

Courses are parent records; enrollments reference course IDs. An optional reference is a separate business decision from a missing required parent. The local example makes those states explicit before any database write occurs.

Prepare the fixture

The fixture has courses C1 and C2. E1 and E2 reference them, E3 references absent C9, and E4 has an intentionally optional null reference. No real student names or account details are included. The application contract permits E4 but records its optional status.

Use Python 3 for the local example. It uses only the standard library and does not require credentials. For the live extension, use a dedicated demonstration namespace and only the permissions needed by the chosen client. Keep connection values outside source files, logs and recordings.

Download the runnable example · Inspect expected local output

python example.py

Build the recorded workflow

Create the synthetic course table first, then load all four source enrollments into a staging table in a fresh namespace. The stage deliberately retains unresolved E3 and the optional null on E4.
Inspect the stage and run the packaged LEFT JOIN report. It distinguishes a resolved reference, a missing parent and an optional null before any final child import is attempted.
Export the complete four-row report and run route-export.py. It checks the returned reference state and emits the ready and held sets. An empty string classified as missing_parent remains held; it is not silently changed into an optional null.
Use the ready set as input to a separately reviewed final import. Preserve E3 and its missing-parent reason for correction. This recorded exercise verifies the staging and prevalidation handoff; final child import, foreign-key enforcement and retry behavior remain separate steps.

Inspect the executable rule

courses={"C1","C2"}
enrollments=[("E1","C1"),("E2","C2"),("E3","C9"),("E4",None)]
result={"ready":[],"held":[]}
for id,parent in enrollments:
    if parent is None or parent in courses: result["ready"].append(id)
    else: result["held"].append({"id":id,"reason":"missing_parent","parent":parent})
assert result["ready"] == ["E1","E2","E4"]
assert result["held"] == [{"id":"E3","reason":"missing_parent","parent":"C9"}]

Interpret the local result

The local ready set is E1, E2 and E4; E3 is held for missing_parent. The optional null survives because the fixture contract permits it. This is a prevalidation result, not a demonstration that the live database accepted a particular schema or enforced a foreign-key rule.

Executed local output

{
  "ready": [
    "E1",
    "E2",
    "E4"
  ],
  "held": [
    {
      "id": "E3",
      "reason": "missing_parent",
      "parent": "C9"
    }
  ]
}

The recorded SQL-to-application walkthrough

The actual staged-reference report and CSV put E1, E2 and E4 in the ready set, while E3 is held with missing_parent for C9. E4 is accepted by the application’s explicit optional-reference rule. The adapter preserves that distinction. This proves the synthetic staging-report-to-application handoff, not that a final child table was populated or a database foreign-key constraint enforced the rule.

Enrollment	Application state	Reason
E1	ready	resolved parent
E2	ready	resolved parent
E4	ready	optional null
E3	held	missing_parent (C9)

The following stills come from actual native console captures. Private fields were blurred before encoding. Open an image for the full-size query or result. Timing shown by the console is one observation, not a benchmark.

The actual enrollment stage retains E1/E2, unresolved E3 and the optional null reference on E4.

The successful report checks staged course references while preserving unmatched and null cases.

The actual result supports ready E1/E2/E4 and held E3; final child import is outside this recorded step.

Reproduce the verified handoff

Executed fixture and report SQL · Actual downloaded CSV · CSV adapter · Executed adapter output · Verification receipt.

Choose a new demonstration namespace for a repeat run. Execute the setup statements, inspect the stored input rows, and then execute this report. Export its complete successful result and pass that actual file to the adapter.

SELECT e.enrollment_id AS enrollment_id, e.course_id AS course_id, c.course_id AS resolved_course_id, CASE WHEN e.course_id IS NULL THEN 'optional_null' WHEN c.course_id IS NULL THEN 'missing_parent' ELSE 'resolved' END AS reference_state FROM academy_blog_20261018_related_records.enrollments_stage e LEFT JOIN academy_blog_20261018_related_records.courses c ON e.course_id = c.course_id ORDER BY enrollment_id;

python route-export.py export.csv

The adapter checks a local CSV and produces application output. It performs no network calls or external actions. The packaged synthetic-contract-input.csv is a separate offline test input; it is not the actual console export.

Diagnose and recover safely

If a retry duplicates a child, inspect the business key and import-attempt record. A new attempt should not silently create a new business entity. If parent IDs change during mapping, regenerate the reference report before proceeding.

Boundaries and the next improvement

Prevalidation is a snapshot and can become stale under concurrent changes. Production imports need a transaction or concurrency strategy supported by the chosen interface. Preserve source records, keep successful and held counts separate, and avoid declaring an entire batch complete because some rows were accepted.

The practical takeaway

Stage first, explain every held record and import only the reviewed ready set. The recorded OriginChainDB report makes the reference decision inspectable before a final write. Add the deployment’s supported concurrency strategy and explicit retry policy before calling a production batch complete.

Continue with the product reference

OriginChainDB SQL documentation. The integration design was checked against the local official documentation and Python client source on 9 October 2026. Source inspection does not establish the behavior of the current deployment.

Local Python execution: sanitized receipt. The recorded SQL-to-CSV scope is verified above. No customer data, performance benchmark or production deployment is claimed.