| name | rdbms-naming |
| description | Common RDBMS naming and data type conventions (single source for MySQL and PostgreSQL). Table/column snake_case, singular, active voice (with date/time column exceptions), prefix/postfix, abbreviation dictionary, column prefix/suffix system, index naming (_IDX/_UIDX/_FTX), data type selection. Triggers: table/column/index design, DDL authoring, schema review, naming conventions, snake_case, abbreviations, PK/FK naming, boolean columns, DECIMAL, settlement amount columns.
|
| origin | custom |
| workloads | ["mysql","postgres"] |
RDBMS Naming Conventions
Naming governance (snake_case, singular, active voice, prefix/postfix, abbreviations, index naming) is
common across MySQL and PostgreSQL, making this skill the single source. Data types differ by
engine, so apply only the target DB column from the "Data Types (by DB)" table below; see
mysql-guideline / postgres-guideline for deeper details.
When to Activate
When creating tables, columns, or indexes; authoring or reviewing DDL; or deciding naming conventions.
Target: RDBMS (MySQL/PostgreSQL).
Common Rules
- snake_case: All identifiers are lowercase with underscores.
authUser → auth_user.
- Descriptive and intuitive: Names should be self-explanatory. Use
delivery_log / order_log instead of vague log. Prefer simple words.
- Active voice: Use active voice for verbs.
create_date (not created_date). Gerunds are allowed.
- Exception — date/time columns: Idiomatic expressions like
created_at / updated_at remain as-is. Strong ORM and framework conventions exempt these from the active voice rule.
- No reserved words: Do not use DB-specific reserved words as identifiers.
(MySQL: dev.mysql.com/doc/refman/8.0/en/keywords.html)
Table / Column
- Singular form: Table names are singular.
users → user.
- No postfix / limited prefix:
tb_user, user_tbl → user. Use prefix only to distinguish attribute tables subordinate to a master table. Examples: user_auth (child of master user), book_like (child of master book).
- Limited abbreviations: Avoid abbreviations when possible. If necessary, use lowercase and register in the abbreviation dictionary for team-wide distribution.
create_dt → create_date, user_cd → user_code.
Column Prefix/Suffix System
| Purpose | Rule | Example |
|---|
| PK | <table>_id | user_id |
| FK | <parent_table>_id | user_id |
| Date (DATE) | <purpose>_date | create_date |
| Date+Time (DATETIME) | <purpose>_at | created_at |
| Code | <purpose>_code | user_code |
| Number | <purpose>_no | order_no |
| Boolean | <column>_yn | use_yn |
Abbreviation Dictionary
Principle: Use abbreviations only for overly long words where the abbreviation is sufficient and agreed upon. Keep rules minimal.
| Full Term | Abbreviation |
|---|
| number | no |
| address | addr |
| episode | ep |
| transaction | tx |
| count | cnt |
| authentication | auth |
| introduce | intro |
Index Naming
Structure: Describe the table and included columns in condition order, and append the suffix in uppercase.
<table>_<col1>_<col2>_..._IDX
| Type | Suffix | Example |
|---|
| General | _IDX | book_like_user_id_IDX |
| Unique | _UIDX | book_uuid_UIDX |
| Fulltext | _FTX | book_name_FTX |
Composite index example: actor(first_name, last_name, last_update) →
actor_first_name_last_name_last_update_IDX.
Data Types (by DB)
Type selection varies by DB engine. Follow the common principles (below), but refer to the target DB column for concrete types. See mysql-guideline / postgres-guideline for deeper type tables.
Common Principles (DB-agnostic)
- PK is integer-based (avoid UUID): The first-column PK
id should be an auto-increment integer
(int/bigint). Ordering, index locality, and storage favor integers over UUIDs.
Use UUID only when distributed generation across multiple DBs or shards is required (no central sequence).
For a single DB, integer PK is the default.
- MySQL:
AUTO_INCREMENT (e.g., id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY)
- PostgreSQL:
GENERATED ALWAYS AS IDENTITY (e.g., id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
- Amounts / settlement: Floating-point (
float/double/real) is absolutely prohibited → use fixed-point.
Default scale (10,2); adjust for currency and precision (KRW (15,2), ratios (5,4)).
- JOIN keys: Use integer types and ensure both sides of the join have identical types.
- Strings: Fixed-length
CHAR(n), variable VARCHAR(n).
- Avoid NULL: For indexed columns, avoid NULL — prefer normalization into separate tables and JOIN.
Allow NULL only when size is small or usage is minimal.
Type Mapping
| Purpose | MySQL | PostgreSQL |
|---|
| Boolean | CHAR(1) 'Y'/'N' recommended (avoids 0→NULL/falsy confusion) or tinyint(1) 0/1. No native boolean | native boolean. 'Y'/'N' strings prohibited |
| PK (auto, integer) | AUTO_INCREMENT — int/bigint unsigned (standard method) | GENERATED ALWAYS AS IDENTITY — int/bigint (standard method). Do not use SERIAL (proprietary; IDENTITY is SQL standard, safer, simpler permissions) |
| Amount | DECIMAL(p,s) | numeric(p,s) |
| Date+Time | datetime (+DEFAULT CURRENT_TIMESTAMP) | timestamptz (timezone required) |
| Date | date | date |
| Long text | TINYTEXT(256B)/TEXT(64KB)/MEDIUMTEXT(16MB)/LONGTEXT(4GB) 4 tiers | Variable text single (no length distinction) |
| JSON | json (8.0+ native) | jsonb (indexing support, not json) |
| Positive-only | UNSIGNED option | No UNSIGNED → CHECK (col >= 0) |
| Fixed display width | INT(n) ZEROFILL | No display width/ZEROFILL concept → handle in app or LPAD |
| External ID (not PK) | char(36)/binary(16) UUID | uuid (gen_random_uuid()) |
| IP address | varchar(45) | inet (native) |
| Array | (none → normalize or JSON) | type[] (e.g., text[]) |
MySQL-specific: UNSIGNED, ZEROFILL, TEXT 4 tiers, AUTO_INCREMENT.
PostgreSQL-specific: native boolean, timestamptz, jsonb, inet, arrays,
IDENTITY, numeric. Do not mix; apply only the target DB column.
Bad / Good Summary
| Bad | Good | Reason |
|---|
authUser | auth_user | snake_case |
users | user | singular |
tb_user | user | unnecessary prefix |
created_date | create_date | active voice (date columns like created_at are exceptions) |
create_dt | create_date | abbreviation overuse |
idx_book_user | book_user_id_IDX | uppercase suffix, table+column order |
Related
mysql-guideline — MySQL-specific defaults, types, and prohibitions. Based on these rules.postgres-guideline — PostgreSQL-specific differences (single text type, IDENTITY, no UNSIGNED, etc.).rdbms-data-modeler agent — Applies these conventions for normalization and table design.
