domain-prefixed table naming

a table name outlives its feature: it is read in every migration, query plan, and generated model. a vague or implementation-shaped name (items, provider_*, *_map) misleads every future schema decision. domain-prefixed singular fact names keep ownership visible, stay aligned with the one glossary term, and make relationship tables honestly signal pure link vs fact with lifecycle. consistent naming makes cross-schema search and review mechanical.

every table name = shortest owning domain keyword + singular semantic fact: <domain>_<fact>. the prefix makes ownership visible in migrations, SQL, observability, and codegen. shortest-exact compound in snake case: process_step, not provider_process_step, when process + step identify the fact.

name after the fact is understood, not after an ORM model exists. ask what stays true if the API, component, provider, and implementation are replaced — that is the fact noun. then pick the shortest domain word that disambiguates it in project scope.

name for the durable business fact, not the implementation:

use the domain prefix even when the fact name looks globally unique. never data, records, items, metadata, mapping.

pick the notation by whether the row carries anything beyond proving the link:

1. pure association — only two foreign keys (plus timestamps). name <owning_domain>_<joined_noun>. <owning_domain> is decided by the same domain-ownership test as every other table ("choose the noun from the durable fact" above): whichever participant's domain the relationship structurally lives under. <joined_noun> is the other participant's singular noun, unqualified. deterministic glossary lookup — never phrasing, alphabetical, or foreign-key declaration order: a relationship defined and read inside the team domain is team_account, full stop; account_team is wrong no matter which foreign key was declared first. foreign keys <owning_domain>_id and <joined_noun>_id, plus a composite unique constraint on both. example: team_account(team_id, account_id) — proves an account belongs to a team, owned by the team domain.
2. relationship with its own lifecycle — carries status, attributes, permissions, or history beyond the two foreign keys. name the fact, not the link: team_membership (role, joined_at, invited_by), billing_entitlement, review_decision.

never *_map, *_link, *_join, *_relation for a relationship with real domain meaning. the moment a case-1 table grows a column beyond its foreign keys and timestamps it is case 2 — rename to the fact.

longer is not more precise: after prefix + fact noun, drop any word that does not resolve a real collision — extra qualifiers slow every read, migration, and generated model without adding information the columns and constraints already prove.

drop on sight:

• cardinality infixes (mtm, many_to_many, m2m) — the composite primary key and foreign keys already prove the shape; name the relationship, not its cardinality.
• redundant type words (_table, _entity, _record) when the name already reads as one thing.
• vendor/transport/UI qualifiers that belong on a column, not the table name (see shortest exact names).

cross-project convention (searched 2026-08-24; no source disagrees on this part): lowercase snake_case — never camelCase, since SQL is case-insensitive and mixed case is unreadable in a query; no bare reserved keywords (user, order, group, session all collide in at least one major engine); singular vs plural is a genuine split, but every source agrees consistency inside one schema beats either side. this project chose singular fact nouns (channel_root, not channel_roots) — keep it, never mix in a plural table.

case: a cardinality infix hiding in plain sight

a bookmark relationship table (account bookmarks a channel-like entity) was named with an explicit mtm infix. the composite primary key on both foreign keys already proved that shape — the infix added a word, no information. owed rename: <owning_domain>_mtm_<joined_noun> → <owning_domain>_<joined_noun>, in a coordinated code + data migration.

• primary key: id.
• foreign key: referenced singular noun + _id — process_id, account_id.
• external identity: <provider>_<object>_id — payment_customer_id.
• authored business key: exact domain noun (slug, handle) with a domain-appropriate unique constraint.
• boolean: is_<state> or has_<capability>; timestamps end in _at.

• review every schema change against the domain glossary: prefix and fact must use canonical terms.
• confirm foreign keys, unique constraints, indexes, ORM names, and migration names share one meaning, no string-derived aliases.
• read a proposed migration as a vocabulary change: one glossary term, one schema name, one generated model, one set of foreign-key nouns.
• a relationship table with extra state must not be mistakable for a pure association from its name.

bad

create table workflow_step_map (
  workflow_id text not null,
  step_id text not null,
  status text not null
);

map hides that the relationship has status — its own lifecycle. readers assume a pure link, so nobody looks for a state machine; permissions, attempts, or history implied by status get skipped in deletion jobs and exports. the name says "implementation shape", not "fact", so every migration, review, and generated model inherits the misdirection and glossary searches for the domain term miss the table.

good

create table workflow_step_record (
  workflow_id text not null,
  step_id text not null,
  status text not null
);

the singular domain-prefixed fact names the durable relationship, not its shape. ownership is visible in every migration, query plan, and generated model; the name matches the glossary term readers search for. a relationship that grows lifecycle columns can no longer hide behind a link-shaped name, and cross-schema search for the domain noun finds exactly the owning tables.

database design · identifiers and keys · domain-keyword organization · shortest exact names · domain glossary