SQL Column Naming Guide
The conventions that keep schemas readable for years: casing, nouns, reserved words, and the per-database rules that actually bite.
Use snake_case
The SQL world runs on snake_case: first_name, created_at, order_id. It is the default in PostgreSQL, MySQL, and SQL Server documentation, it survives every driver's identifier handling, and it reads well in long SELECT lists. Generate candidates with the free database column name generator in snake_case unless your existing schema demands otherwise.
Singular nouns, descriptive words
Columns hold one value per row, so name them in the singular: email not emails, phone_number not phone_numbers. Be descriptive but concise — customer_order beats both co and customer_purchase_order_record. A new developer should be able to guess the contents from the name alone.
Avoid reserved words
Some innocent-looking words are reserved in SQL: order, user, group, select, table, key, index. Using them forces quoted identifiers everywhere and breaks ORMs in creative ways. When the domain word is reserved, qualify it: order_id, user_name, sort_order. See common mistakes for the full rogues' gallery.
Know your database's quirks
| Database | Rule worth knowing |
|---|---|
| PostgreSQL | Unquoted identifiers are folded to lowercase. A column created as firstName becomes firstname unless double-quoted everywhere — design in lowercase from the start. |
| MySQL / MariaDB | Case sensitivity of identifiers depends on the operating system and configuration. Design case-insensitively: never rely on User_ID vs user_id being different. |
| SQL Server | Collation determines case sensitivity. Bracket-quoting ([order]) works but signals a naming failure — rename instead. |
| Oracle | Older versions limit identifiers to 30 characters. Keep names short if Oracle compatibility matters. |
The safe intersection: lowercase snake_case, no reserved words, under 30 characters. A name that satisfies all four databases can move between them untouched.
Type-revealing affixes
The best column names reveal their type without a schema lookup:
- Booleans:
is_,has_,can_prefixes —is_active,has_avatar,can_edit - Timestamps:
_atsuffix for instants —created_at,deleted_at;_datefor calendar dates —birth_date - Keys:
<table>_id—user_id,order_id, matching the referenced primary key name exactly - Amounts: say what is measured —
total_amount,unit_price, nottotal
The pattern catalog lists every standard affix with examples, and naming by column type shows them in context.
Don't repeat the table name on every column
user_first_name inside a users table is noise — first_name is unambiguous in context. The exception is keys: user_id as a foreign key in orders should match the primary key name in users exactly, which makes joins self-documenting.