MariaDB Schema Design

Decisions made at CREATE TABLE time outlive any application layer. This skill covers primary-key type choice, multi-tenant data layout, ROW_FORMAT, CHARSET / collation, foreign-key constraints, generated columns, and the Frappe / ERPNext companion conventions. Read this before the first DDL of a new project.

Quick Reference

• ALWAYS default to BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY on single-writer InnoDB tables : monotonic, B-tree friendly, 8 bytes per row.
• ALWAYS use BINARY(16) (not CHAR(36)) when a UUID is required, with UUID_TO_BIN(uuid, 1) to apply the swap-flag for time-low/high ordering ; CHAR(36) bloats every secondary index by ~9x.
• ALWAYS prefer ULID or UUIDv7 over UUIDv4 for distributed-write designs : time-prefixed sortable keys preserve B-tree locality.
• ALWAYS declare ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci per-table on 10.6-LTS and 10.11-LTS : stock binaries still default to latin1 and emoji silently fail.
• ALWAYS set innodb_default_row_format=dynamic and innodb_large_prefix=1 at server level when running Frappe / ERPNext or any utf8mb4 schema with VARCHAR(255) indexes ; the 767-byte legacy prefix limit blocks utf8mb4 indexes (255 * 4 = 1020 bytes) on COMPACT row format.
• NEVER use utf8 as the charset declaration : it is a 3-byte alias for utf8mb3 and cannot store characters above U+FFFF (emoji, many CJK, mathematical symbols).
• NEVER pick CHAR(36) UUID-text PK on a write-heavy InnoDB table : random insert positions in the clustered index fragment pages and force page-splits on every batch.
• NEVER apply schema-per-tenant beyond a few hundred tenants : mysql metadata, open-file-handles, and per-DB connection overhead degrade non-linearly past ~1000 schemas.
• NEVER design a multi-tenant row layout without INDEX(tenant_id, ...) on every common query path : the leftmost-prefix rule means single-column indexes ignoring tenant_id cannot serve the planner.
• NEVER apply ON DELETE CASCADE on a huge child table : a single parent DELETE acquires X-locks on every matching child row and may run for hours.
• MariaDB JSON is a LONGTEXT alias, not native binary (per D-010). Use CHECK (JSON_VALID(col)) for structure, and functional indexes on VIRTUAL columns for index access (ADD COLUMN x AS (JSON_VALUE(doc,'$.k')) VIRTUAL, ADD KEY ix(x)).
• Frappe / ERPNext tables follow tab<DoctypeName> naming (literal tab prefix, with embedded spaces allowed inside backticks, for example `tabSales Invoice`). Child tables include parent, parentfield, parenttype, idx columns.