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