> ## Content Index
> Fetch the complete content index at: https://blogs.originchaindb.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Design an import order for related records
- URL: https://blogs.originchaindb.com/related-record-import-order/
- Published: 2026-10-18T04:30:46.000Z
- Updated: 2026-11-07T16:41:52.000Z
- Description: 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.
- Author: OriginChainDB Team
- Tags: SQL & data, OriginChainDB, sql, tutorial, #migrated-website, #byline-originchaindb-team

Suppose an education app is importing courses and enrollments. Each enrollment can refer to a course by its ID. If that course is missing, the app cannot tell which course the enrollment belongs to.

The approach here is to check the incoming records before the final import. Keep the valid records ready for the next step, and hold the unresolved records with a reason so someone can correct them. A problem in one record does not have to hide the state of the rest.

**The example:** courses C1 and C2 exist. Enrollments E1 and E2 point to them, so they are ready. E3 points to missing course C9, so it is held. E4 has no course reference and is ready only because this example explicitly allows that reference to be optional.

**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](https://originchaindb.com/academy/2026-10-12/related-record-import-order/assets/hero-light.svg)

Conceptual illustration of the synthetic application workflow. [Light PNG](https://originchaindb.com/academy/2026-10-12/related-record-import-order/assets/hero-light.png?ref=blogs.originchaindb.com) · [Dark PNG](https://originchaindb.com/academy/2026-10-12/related-record-import-order/assets/hero-dark.png?ref=blogs.originchaindb.com). This is not a product screenshot.

## Data rules

A few terms used below:

- **Parent and child records:** the course is the parent; an enrollment that refers to it is a child. These names describe the dependency.
- **Reference:** the course ID stored on an enrollment. `NULL` represents an empty reference in this example; it differs from an ID that was supplied but does not match a course.
- **Staging:** keeping the incoming rows in a working table while you check them, before copying approved rows into their final table.

The **data contract** is the rule your application follows about which references are required and which may be empty. The local example checks these rules before any database write.

## Set up the sample data

The sample dataset contains 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](https://originchaindb.com/academy/2026-10-12/related-record-import-order/resources/example.py?ref=blogs.originchaindb.com) · [Inspect expected local output](https://originchaindb.com/academy/2026-10-12/related-record-import-order/resources/expected-output.json?ref=blogs.originchaindb.com)

```
python example.py
```

## Follow the steps

1. 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.
2. 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.
3. 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.
4. 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.

## Understand the code

The Python example below checks each enrollment against the known course IDs. A matching ID goes into `ready`. A non-matching ID goes into `held`, together with the missing course ID. `None` is Python’s empty value; this sample accepts it under its optional-reference 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"}]
```

## Check the result

The local ready set is E1, E2 and E4; E3 is held for missing\_parent. The optional null is retained because the sample’s rules allow 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"
    }
  ]
}
```

## Use the result in your application

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.

[![Actual light-mode OriginChainDB console. The actual enrollment stage retains E1/E2, unresolved E3 and the optional null reference on E4.](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/01-live-inputs.png)](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/01-live-inputs.png?ref=blogs.originchaindb.com)

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

[![Actual light-mode OriginChainDB console. The successful report checks staged course references while preserving unmatched and null cases.](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/02-live-operation.png)](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/02-live-operation.png?ref=blogs.originchaindb.com)

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

[![Actual light-mode OriginChainDB console. The actual result supports ready E1/E2/E4 and held E3; final child import is outside this recorded step.](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/03-live-result.png)](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/03-live-result.png?ref=blogs.originchaindb.com)

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

## Run the example yourself

The SQL report uses a `LEFT JOIN` to keep every staged enrollment, even when its course is missing. `CASE` labels each row as `resolved`, `missing_parent` or `optional_null`. The CSV adapter then reads those labels and separates the ready and held records.

**Ready means checked, not imported.** The report and adapter prepare the next step. The final child import needs its own review and execution, as described below.

[Executed fixture and report SQL](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/executed-fixture-and-report.sql?ref=blogs.originchaindb.com) · [Actual downloaded CSV](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/export.csv?ref=blogs.originchaindb.com) · [CSV adapter](https://originchaindb.com/academy/2026-10-12/related-record-import-order/resources/live-preflight-20261010/route-export.py?ref=blogs.originchaindb.com) · [Executed adapter output](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/adapter-result.json?ref=blogs.originchaindb.com) · [Verification receipt](https://originchaindb.com/academy/2026-10-12/related-record-import-order/evidence/live-console-20261010/export-verification.json?ref=blogs.originchaindb.com).

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.

## Troubleshoot problems

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.

## What this example does not cover

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.

## What to remember

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.

## Read the documentation

[OriginChainDB SQL documentation](https://originchaindb.com/docs/sql?ref=blogs.originchaindb.com). 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](https://originchaindb.com/academy/2026-10-12/related-record-import-order/resources/local-execution.json?ref=blogs.originchaindb.com). The recorded SQL-to-CSV scope is verified above. No customer data, performance benchmark or production deployment is claimed.