# Database Naming Guideline

Adapted from `PLSQL-and-SQL-Coding-Guidelines.pdf` (Trivadis, Version 4.4).
Applies to all projects regardless of database engine (MySQL, SQLite, etc.).

## General rules

1. Never use names with a leading numeric character.
2. Always choose meaningful and specific names.
3. Avoid abbreviations unless the full name is excessively long.
4. Keep abbreviations short — shorter than 5 characters.
5. Use only widely known and accepted abbreviations.
6. Maintain a glossary of all accepted abbreviations.
7. Never use SQL reserved keywords as names.
8. Avoid redundant or meaningless prefixes/suffixes (e.g. don't name a table `emp_table`).
9. Use one spoken language for all objects in a project (pick one and stick to it).
10. Always use the same name for elements with the same meaning across the project.

## Case

Most databases fold unquoted identifiers to lowercase. **Write all names in
lowercase `snake_case`.** Never use quoted identifiers to force mixed case — it
creates portability problems and makes every reference more verbose.

## Procedural SQL identifiers

Name identifiers by what they represent — no prefixes.

| Identifier        | Example              |
| ----------------- | -------------------- |
| Local variable    | `version`            |
| Global variable   | `app_name`           |
| Cursor            | `employee`           |
| Record            | `employee`           |
| Parameter         | `employee_id`        |
| Exception         | `employee_exists`    |
| Constant          | `max_retries`        |

## Database object naming

| Object | Convention | Examples |
| ------ | ---------- | -------- |
| **Table** | Singular name of what each row represents. | `employee`, `department`, `user_role` |
| **Temporary table** | Named like tables; suffixed `_tmp`. | `employee_tmp`, `import_row_tmp` |
| **Column** | Singular name of what is stored. Use suffixes that communicate the nature of the data (see Column suffixes below). | `first_name`, `created_at`, `user_id` |
| **Primary key column** | `<table>_id`. | `employee_id` in `employee`, `project_id` in `project` |
| **View** | Plural name of what it contains; suffix `_v` when a 1:1 view layer sits above the table. | `active_orders`, `employee_v` |
| **Primary key constraint** | Table name (or abbreviation) + `_pk`. | `employee_pk`, `user_role_pk` |
| **Unique key constraint** | Table name (or abbreviation) + role + `_uk`. | `employee_email_uk`, `project_slug_uk` |
| **Foreign key constraint** | Child table abbreviation + parent table abbreviation + `_fk` + optional number. | `empl_dept_fk`, `usr_proj_fk1` |
| **Check constraint** | Table name (or abbreviation) + column/role + `_ck` + optional number. | `employee_salary_min_ck`, `order_status_ck` |
| **Index** | Constraint-serving indexes (PK/UK/FK) share the constraint name. Other indexes: table + columns (or purpose) + `_idx`. | `employee_last_name_idx`, `order_created_at_idx` |
| **Sequence** | Table name (or abbreviation) the sequence feeds + `_seq`. | `employee_seq`, `order_number_seq` |
| **Function** | Verb + noun; name answers "What is the result?" Avoid `get_` prefix (a function always returns something). | `employee_by_id`, `total_by_project` |
| **Procedure** | Verb + noun; name answers "What is done?". | `calculate_salary`, `archive_old_orders` |
| **Trigger** | Table name + timing/event + `_trg`. Timing: `bi` before insert, `ai` after insert, `bu` before update, `au` after update, `bd` before delete, `ad` after delete. | `employee_bi_trg`, `order_au_trg` |

## Column suffixes

Suffixes make the nature of a column readable without inspecting its type.

| Suffix | Meaning | Examples |
| ------ | ------- | -------- |
| `_id` | Identifier — primary key or foreign key | `user_id`, `project_id` |
| `_at` | Point in time (datetime / timestamp) | `created_at`, `updated_at`, `deleted_at` |
| `_on` | Calendar date (date only) | `born_on`, `expires_on` |
| `_count` | Integer quantity | `comment_count`, `retry_count` |
| `_name` | Human-readable name | `first_name`, `last_name`, `display_name` |
| `_type` | Discriminator / category | `account_type`, `event_type` |
| `_status` | Current state from a defined set | `order_status`, `payment_status` |
| `_url` | Full URL | `avatar_url`, `redirect_url` |
| `_path` | File system or URL path segment | `file_path`, `upload_path` |
| `_hash` | Hashed value | `password_hash`, `content_hash` |
| `_token` | Opaque token or secret | `reset_token`, `api_token` |
| `_code` | Short code or abbreviation | `country_code`, `currency_code` |

Boolean columns use an adjective without a suffix — they read as a true/false
statement: `active`, `verified`, `published`, `enabled`.