SQL Naming & Structure Guidelines (System Contract)

This document defines how SQL tables, columns, and foreign keys must be named so that CTXT, SQLfunction, and ArrayTemplator can generate forms and templates automatically without any configuration.

1. General naming rules

Use lowercase_snake_case everywhere.

Tables are singular:

user

product

order_item

Pivot (many-to-many) tables use both table names joined with "_", in alphabetical order:

role_user

post_tag

Columns also use lowercase_snake_case.

Foreign key columns always end with "_id":

user_id → user.id

status_id → status.id

This consistent pattern allows the generator to automatically detect relationships.

2. Required columns for FK-target tables

Any table referenced by a foreign key must include:

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

name VARCHAR(255) NOT NULL

The system must always be able to run:

SELECT id, name FROM table ORDER BY name;

For clarity and maintainability, these metadata columns are recommended:

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

deleted_at DATETIME NULL

3. Foreign key column and constraint rules

Foreign key columns follow:

something_id INT UNSIGNED NOT NULL

Foreign key constraint names follow:

fk_table_referenced_table

Example:

CREATE TABLE post (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

body TEXT NOT NULL,

user_id INT UNSIGNED NOT NULL,

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP,

CONSTRAINT fk_post_user

FOREIGN KEY (user_id)

REFERENCES user(id)

ON UPDATE CASCADE

ON DELETE RESTRICT

);

Avoid multi-column foreign keys.

The system expects a simple one-column reference.

4. Lookup / reference tables (statuses, types, dropdowns)

Use lookup tables instead of ENUM when values must be selectable.

Every lookup table must contain:

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

name VARCHAR(255) NOT NULL

Optional additional columns:

code VARCHAR(64) UNIQUE

sort INT UNSIGNED NOT NULL DEFAULT 0

Example:

CREATE TABLE status (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

code VARCHAR(64) UNIQUE,

sort INT UNSIGNED NOT NULL DEFAULT 0,

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP

);

Referencing example:

CREATE TABLE order_header (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

status_id INT UNSIGNED NOT NULL,

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP,

CONSTRAINT fk_order_header_status

FOREIGN KEY (status_id)

REFERENCES status(id)

);

The generator can always build dropdowns with:

SELECT id, name FROM status ORDER BY name;

5. Info-schema and generator expectations (the contract)

The automatic generator relies on these assumptions:

Each referenced table provides:

id (INT UNSIGNED AUTO_INCREMENT PRIMARY KEY)

name (human-readable label)

Foreign keys can be discovered through information_schema, giving:

COLUMN_KEY

DATA_TYPE

COLUMN_TYPE

FK_TABLE

FK_COLUMN

FK_REF = FK_TABLE.FK_COLUMN (e.g., user.id)

From this information the generator determines:

elem (input/select/textarea/file/etc)

options (for lookups or enums)

type (html5 type: date, time, timestamp)

enctype (multipart/form-data when needed)

fk (table.column pairing)

Because of this, the following must always be true:

Every FK target table contains: id, name

Every FK column ends with: _id

Every FK dropdown query can safely be:

SELECT id, name FROM table ORDER BY name;

6. Minimal fully compliant examples

Entity table:

CREATE TABLE user (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

email VARCHAR(255) NOT NULL UNIQUE,

password VARCHAR(255) NOT NULL,

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP

);

Referencing table:

CREATE TABLE post (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

body TEXT NOT NULL,

user_id INT UNSIGNED NOT NULL,

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP,

CONSTRAINT fk_post_user

FOREIGN KEY (user_id)

REFERENCES user(id)

);

7. Summary of the contract

All FK-target tables must have id and name.

All FK columns must end in _id.

Lookup tables follow the same id/name rule.

The generator assumes:

SELECT id, name FROM table ORDER BY name;

is always valid for any foreign key target.

Following these rules ensures CTXT, templates, and automatic form generation work flawlessly without any per-table configuration.