Table Column Naming Conventions

EMPS doesn't enforce column names at the database level — sqlsync will happily create a table with any columns you like. But several built-in helpers and components assume a handful of conventional names. Following them means you get autocompletion, labels, and sorting for free; deviating from them means writing a small override every time. This page lists the conventions worth knowing before you design a new table.

name — the Default Display Column

The single most useful convention: if a table has one main human-readable text field, call it name.

This isn't just a style preference — <selector> and <descriptor> in entity-search mode (type="table_name" :search="true") query and display a column literally called name by default, with zero configuration:

<selector type="authors" v-model="row.author_id" :search="true"></selector>

If authors.name exists, the picker above works immediately — searches name, displays name. No PHP to write.

If instead the column is called title, label, short, or split across short/fullname, the default lookup finds nothing (or displays a blank label), and you have to add a per-table case to modules/pick/ng/list/project.php and modules/pick/ng/describe/project.php — see Selectors and Descriptors for exactly how. That's not hard, but it's boilerplate you can skip entirely by naming the column name up front.

Rule of thumb: unless the table genuinely needs two distinct label variants (e.g. a short abbreviated form and a full form, like a remedy's short/fullname pair), the primary text column should be name. If you also need a separate display/title field with different semantics (e.g. a book's title as opposed to a generic name), that's fine — just know you're opting into writing a pick/ng override for it.

A Real Example

A project added a books table with a title column (reasonable — that's what books have) instead of name. Later, adding a <selector type="books"> picker to another form silently returned no search results, because the default pick/ng behavior was querying a nonexistent books.name column. The fix required extending both pick/ng files with a books-specific case. Naming the column name from the start would have avoided that entirely.

Other Common Columns

These aren't as load-bearing as name, but show up across most EMPS projects and are worth reusing rather than inventing your own variants:

  • id — bigint NOT NULL AUTO_INCREMENT, primary key. See the table examples on the SQLSync page.
  • cdt / dt — created / last-modified timestamps, stored as bigint Unix timestamps (not DATETIME). Used throughout EMPS_DB helpers; see EMPS_DB.
  • status (or a more specific name like pub for publish state) — an int/bigint state column paired with an entry in enum.nn.txt, often alongside a matching ..._class enum that maps each code to a Bulma color class for badges (is-success, is-dark, etc.). See Enum Values.
  • <other_table>_id — foreign key columns are named after the table they point to, singular, plus _id (author_id, remedy_id, book_id). This is what both <selector>/<descriptor> and hand-written join code expect.

See Also

  • Selectors and Descriptors — the component that depends on the name convention, and how to override it when you can't follow it.
  • SQLSync — defining the table itself.
  • Enum Values — conventions for status-like columns.