Add a "Design a Pipeline" how-to guide (design method + red-flag checklist)

[dimitri-yatsenko]

Summary

Add a consolidated how-to guide for designing a DataJoint pipeline end to end — a step-by-step design method plus a red-flag checklist — that links the existing explanation pages rather than restating them.

Motivation

The docs already explain the design concepts thoroughly:

• Relational Workflow Model — the schema is the workflow
• Normalization — one table per entity type; the Workflow Normalization and Intrinsic Attributes principles
• Entity Integrity — the primary key defines the entity; natural vs. surrogate keys; schema dimensions
• Data Integrity / Referential Integrity
• Computation Model + the make() reproducibility contract
• Fan-Out Ingestion

What's missing is a single procedural guide that ties these together — "how do I go from a scientific domain to a well-formed schema, in what order, and how do I check my work?" New designers pick up the concepts piecemeal but lack a design method and a quick way to catch common mistakes. There are design-adjacent how-tos (Design Primary Keys, Model Relationships) but no top-level "design a pipeline" walkthrough + checklist.

Proposal

Add how-to/design-a-pipeline.md (a Diátaxis how-to, beside design-primary-keys.md) in three parts:

1. The stance (brief; links out)

Open with the three load-bearing ideas, each linking to the explanation page that develops it — no restating:

• Model the real world, not the data. Entities are real-world referents (objects, events, agents, concepts); files, rows, and IDs are how entities are represented, not entities themselves.
• The schema is the workflow. A foreign key is both referential integrity and a dependency edge, so the FK/PK graph is the causal/computational order of the work.
• The primary key defines the entity. The PK answers "what is one row of this table?" — finding the minimal set of facts that make an entry unique is the highest-leverage design act.

2. A step-by-step design method

1. Enumerate the real-world entities and relationships (objects, events, agents, concepts) — set files and tables aside.
2. Draw the dependency DAG in causal/temporal order (the future FK graph and compute order).
3. For each entity, answer "what is one row?" → its primary key; prefer natural, cascading keys, surrogate only when there is no stable natural identity.
4. Classify each node: entity vs. coordinate (lives inside a PK) vs. part (attaches to a master).
5. Place each attribute on the entity whose key determines it.
6. Assign table tiers by how a row comes to exist (Lookup / Manual / Imported / Computed).
7. Push parameters and interpretation downstream; keep raw ingest parameter-free.
8. Transcribe to DDL and verify (joins are natural on shared PK attributes; cascade order matches causality; keys are complete; every attribute commented).
9. Name honestly, in the domain's vocabulary.

3. A red-flag checklist (common design smells)

• dj.U in a key_source → a grouping that should be a table (missing entity).
• Surrogate id on everything + join tables everywhere → lost cascade and lineage.
• A "file" table the science hangs off → the artifact was modeled, not the event it represents.
• One table = object and event → conflated identities; split them.
• An attribute that varies along an axis not in the table's key → misplaced attribute.
• A key "unique only because a column is currently blank" → incomplete primary key.
• Runtime/user data in Lookup, or code-seeded reference data in Manual → tier mismatch.
• Spine tables for pure coordinates (a table with no per-instance state) → over-modeling; use PKs.
• A direct insert into a Computed/Imported table → rejected; rows come from make().
• A make() call argument that changes the result → an unmapped parameter; make it an upstream table.
• make() reading other rows of its own table / aggregating across keys → move the roll-up to a downstream table keyed on a real parent.

Placement / Diátaxis

A how-to (the procedure) that links the explanation pages (the concepts) and the autopopulate / fan-out specs (the compute contract). Complements the existing design-primary-keys and model-relationships how-tos; likely warrants a nav entry under How-To.

Open questions

• One combined guide, or method and checklist as separate pages?
• Inline worked mini-examples, or lean on the existing tutorials for end-to-end walkthroughs?

[dimitri-yatsenko]

I am closing this issue until a better formulation.