ERPNext DocTypes & Database Relationships

🗄️ DocType vs Database Table vs Document

Part 1's Database Architecture section introduced the core chain — a DocType is metadata, the framework generates a MariaDB table from it, and each saved record is one row. This article assumes that chain and goes underneath it: not just that DocTypes become tables, but exactly how, and what "metadata-driven" means literally rather than as a slogan.

The phrase "the schema is data" in Frappe is not a metaphor — it is a literal description of the storage model. A DocType's own definition (its name, module, permissions, and settings) is one row in tabDocType, and every field it has — label, fieldtype, options, mandatory flag, and so on — is a row in tabDocField with parent pointing back at that DocType. In other words, tabDocType and tabDocField are themselves ordinary Frappe tables generated from a DocType (DocType is a DocType), which is why a fresh Frappe install can bootstrap its own schema system from nothing but a handful of seed JSON files.

This has a concrete, mechanical consequence when you add a field through Customize Form in the UI:

A brand-new field added to an existing DocType typically writes a Custom Field record; a change to an existing field's label, mandatory flag, or read-only state typically writes a Property Setter record. Either way, on the next site migration (or an immediate refresh in developer mode) the framework compares the DocType's full field list against the live table structure and issues the necessary ALTER TABLE ... ADD COLUMN statements — you never hand-write SQL to add a column that a DocType field already describes.

🏷️ The tab<DocType> Naming Convention

This is verified, stable Frappe behavior, unchanged across recent versions: every non-child, non-virtual DocType's table name is the literal string tab concatenated with the DocType's name — spaces preserved, not converted to underscores or camelCase.

Because MariaDB identifiers with spaces require backtick-quoting, raw SQL against these tables looks like SELECT * FROM \`tabSales Invoice\` WHERE customer = 'CUST-00001' — one more reason the Frappe ORM (frappe.get_all, frappe.get_doc) is preferred over hand-written queries, beyond the permission and validation concerns covered in Common Database-Design Mistakes below.

Naming Series & autoname: how the primary key is generated

Every Frappe document's primary key is its name field — not an auto-incrementing integer id. How that name value is produced is controlled by the DocType's autoname setting, which takes one of a few forms:

This matters beyond cosmetics: whatever value ends up in name is exactly what every Link field pointing at that DocType stores as its foreign key. A Sales Order Item.parent value of "SAL-ORD-2026-00042", or a Sales Invoice.customer value of "CUST-00001", is not a numeric surrogate key looked up through a separate id column — it is the primary key, and it is a human-readable string by design.

📚 Master Tables vs Transaction Tables (Expanded)

Part 1's table map section introduced this split briefly; here is the fuller comparison, plus the "why" behind it.

Master data exists so that a fact is stored exactly once and every transaction merely references it via a Link field, rather than copying it. A customer's territory, for example, lives once on the Customer record — thousands of Sales Orders reference it by customer Link rather than each carrying its own copy of the territory string. This is the normalized-database version of "don't duplicate the fact, reference it," and it's why correcting a customer's address once instantly reflects correctly everywhere that customer is referenced, instead of requiring a bulk update across every historical transaction.

🧬 Child Tables in Depth

Part 1's Child Tables Explained section covered the four framework-managed fields — parent, parenttype, parentfield, idx — using a Sales Order / Sales Order Item example. The mechanism is identical everywhere it's used; here's a different concrete instance, Purchase Invoice and its line items, to make the pattern recognizable rather than memorized from one example:

tabPurchase Invoice
       │
       │ parent (1) ──── (N) rows
       ▼
tabPurchase Invoice Item
  parent      = "ACC-PINV-2026-00017"
  parenttype  = "Purchase Invoice"
  parentfield = "items"
  idx         = 1
  item_code   = "RM-CTRL-BOARD"
  qty         = 50
  rate        = 12.40

Three properties of child tables are worth stating explicitly, because they don't hold for normal DocTypes:

• "Is Child Table" is a checkbox on the DocType definition itself. It changes how the framework treats every document of that DocType — it has no independent List View in the Desk UI, because a child row without its parent's context isn't a meaningful unit to browse.
• Child rows are saved as one batch with the parent, not as separate document saves. When a Purchase Invoice with 50 line items is saved, the framework issues one coordinated INSERT/UPDATE/DELETE batch against tabPurchase Invoice Item alongside the single write to tabPurchase Invoice — it is not 50 independent document-save transactions.
• This is why child table edits don't appear as separately-numbered documents in version history. The parent document's version log records the whole document's state at each save, including its child rows, rather than treating each child row as its own versioned entity with its own history.

🗺️ Expanded Table Map by Module

As in Part 1's Illustrative Table Map, these are representative, long-stable core DocTypes grouped by module — not an exhaustive schema dump. Exact table names, and which of these exist at all, can vary by ERPNext version and by which apps are installed on a site; confirm against your installed site (bench --site SITE console, or the DocType list in Developer Mode) before relying on them in code.

🔗 Link vs Dynamic Link vs Table vs Table MultiSelect

These four field types are how DocTypes reference other DocTypes — each solves a different relationship shape:

Two related, UI-level field behaviors are easy to mistake for database constraints, so it's worth being precise about them: fetch_from auto-populates one field's value from a field on the linked document the moment the Link is set (e.g. a Sales Invoice's customer_name field can be configured to fetch from the linked Customer's customer_name field) — it's a convenience copy made at data-entry time, not a live join, so it can go stale if the source document changes afterward. read_only and depends_on similarly control how a field behaves in the form (whether it's editable, whether it's shown at all) purely at the UI layer — neither adds or removes an actual database constraint on the underlying column.

🔀 ER Diagram: Customer (Expanded)

Part 1's ER Diagrams section showed this relationship as a single collapsed row. Expanded, each referencing DocType carries its own illustrative fields:

Contact and Address deserve a specific callout as the canonical real-world example of Dynamic Link introduced above: they are not linked to Customer via a plain fixed Link field. A single Contact or Address DocType instance can belong to a Customer, a Supplier, an Employee, or any other party-like DocType — which one is decided per-record by the link_doctype field, with link_name then holding that target's name. This is precisely why Frappe needs the Dynamic Link field type at all: an ordinary Link can only ever point at one hardcoded target DocType, and a shared "address book" concept spanning several unrelated party types can't be modeled with a fixed Link.

🔀 ER Diagram: Item (Expanded)

Bin is the table worth understanding specifically here: it holds the current on-hand quantity (and reserved/ordered/planned quantities) for one Item in one Warehouse, kept in sync as a derived cache from every posting to Stock Ledger Entry. The relationship is the same "current balance vs full ledger" distinction covered generally for financial balances in the accounting curriculum's reconciliation-flavored level: Bin is a fast-to-read snapshot of current state (one row per Item × Warehouse), while Stock Ledger Entry is the full, append-only movement history that snapshot is derived from — if the two ever disagree, the Stock Ledger Entry history is the source of truth and Bin gets recalculated (a Stock Reconciliation), not the other way around.

🔀 ER Diagram: Accounting (Expanded)

GL Entry is ERPNext's concrete implementation of the abstract "General Ledger" concept described more generally in Invoice vs Payment: The Complete ERP Accounting Architecture's relationship map. Every GL Entry row carries separate debit and credit amount columns — following standard double-entry convention rather than a single signed amount — and links back to whichever document generated it via a voucher_type / voucher_no pair (e.g. voucher_type = "Sales Invoice", voucher_no = "ACC-SINV-2026-00088"). That pair is the direct ERPNext equivalent of the generic source_type / source_id columns described in that article's journal_entry schema — a polymorphic pointer back to whatever business document was the cause of this ledger posting, expressed with real Frappe field names instead of a generic schema sketch.

🧭 Document Flow & Linked Documents

ERPNext documents chain together through ordinary reference fields — a Sales Invoice stores which Sales Order and which Delivery Note it was raised against, a Payment Entry stores which invoice(s) it settles. Because Frappe tracks Link fields generically, opening any document also renders a Connections panel showing every other document that links to it, in both directions — you don't have to know in advance which DocTypes reference a given record to find them.

Two status mechanisms are worth calling out precisely, because they're easy to conflate:

• outstanding_amount — a computed field on Sales Invoice (and similarly on Purchase Invoice) that tracks the unpaid balance as Payment Entries are submitted against it. This is the ERPNext-specific implementation of the generic "Outstanding Balance" formula described in the invoice lifecycle article — invoice total minus allocated payments minus credit notes, kept current as a stored, recalculated field rather than computed fresh on every read.
• docstatus vs status — docstatus is a core, verified Frappe convention present on every submittable DocType: 0 = Draft, 1 = Submitted, 2 = Cancelled, and nothing else. Layered on top of it, most transactional DocTypes also carry a separate, human-readable status field — values like "Overdue", "Paid", or "Return" — that is computed from docstatus plus business logic (due dates, outstanding amount, linked return documents), not stored as the source of truth itself. This mirrors the multi-dimensional status modeling idea from the ERP accounting status matrix: one raw lifecycle flag plus several derived, human-facing labels layered on top, rather than one field trying to capture every dimension of a document's state at once.

⚠️ Common Database-Design Mistakes When Extending ERPNext

🗺️ What's Next

This article expanded on Part 1's Database Architecture, Child Tables, Table Map, and ER Diagrams sections — the same underlying model, in the depth needed to design real customizations against it safely.

From here, Part 5 (Module Integration: Accounting & Stock) shows how these same tables interact live during a real transaction — what actually gets written to GL Entry and Stock Ledger Entry the moment a Sales Invoice is submitted. Part 6 (The Customization Guide) covers how to safely add fields and DocTypes of your own on top of everything described here, without hitting the mistakes listed above.