# Database Naming Guidelines

Naming conventions extracted from `PLSQL-and-SQL-Coding-Guidelines.pdf`
(PL/SQL & SQL Coding Guidelines, Version 4.4). This is the naming authority for
SQL and PL/SQL work in this environment.

## 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 keywords as names (see dictionary view `v$reserved_words`).
8. Avoid redundant or meaningless prefixes/suffixes (e.g. don't name a table `emp_table`).
9. Use one spoken language (English, German, French, …) for all objects in an application.
10. Always use the same name for elements with the same meaning.

## Case

The Oracle Database is not case sensitive with names (`personname` = `PersonName`
= `PERSONNAME`). **Write all names in lowercase and avoid double-quoted
identifiers.** Never enclose object names in double quotes to force mixed/lower
case in the data dictionary.

## PL/SQL identifiers

Follow a `{prefix}variablecontent{suffix}` pattern.

| Identifier                   | Prefix | Suffix  | Example             |
| ---------------------------- | ------ | ------- | ------------------- |
| Global variable              | `g_`   |         | `g_version`         |
| Local variable               | `l_`   |         | `l_version`         |
| Cursor                       | `c_`   |         | `c_employees`       |
| Record                       | `r_`   |         | `r_employee`        |
| Array / Table                | `t_`   |         | `t_employees`       |
| Object                       | `o_`   |         | `o_employee`        |
| Cursor parameter             | `p_`   |         | `p_empno`           |
| In parameter                 | `in_`  | `_type` | `in_empno`          |
| Out parameter                | `out_` | `_type` | `out_ename`         |
| In/Out parameter             | `io_`  |         | `io_employee`       |
| Record type definition       | `r_`   | `_type` | `r_employee_type`   |
| Array/Table type definition  | `t_`   |         | `t_employees_type`  |
| Exception                    | `e_`   |         | `e_employee_exists` |
| Constant                     | `co_`  |         | `co_empno`          |
| Subtype                      |        |         | `big_string_type`   |

## Database object naming

All object names below may **optionally be prefixed by a project abbreviation**
(e.g. `sct_contracts`).

| Object | Convention | Examples |
| ------ | ---------- | -------- |
| **Table** | Plural name of what it contains (singular if it always holds exactly one row). Suffix `_eb` when protected by an editioning view. Add a dictionary comment for the table and every column. | `employees`, `departments`, `countries_eb` |
| **Temporary table (GTT)** | Named like tables; optionally suffixed `_tmp`. | `employees_tmp`, `contracts_tmp` |
| **Column** | Singular name of what is stored (plural if the column data type is a collection). Add a dictionary comment. | |
| **View** | Plural name of what it contains; optionally suffixed with a view indicator (e.g. `_v`) — mostly when a 1:1 view layer sits above the table layer. Editioning views are named like the underlying table. Add dictionary comments. | `active_orders`, `orders_v`, `countries` (editioning view for `countries_eb`) |
| **Primary key constraint** | Table name (or abbreviation) + `_pk`. | `employees_pk`, `departments_pk`, `sct_contracts_pk` |
| **Unique key constraint** | Table name (or abbreviation) + role + `_uk` + optional number. | `employees_name_uk`, `departments_deptno_uk`, `sct_icmd_uk1` |
| **Foreign key constraint** | Table abbreviation + referenced table abbreviation + `_fk` + optional number. | `empl_dept_fk`, `sct_icmd_ic_fk1` |
| **Check constraint** | Table name (or abbreviation) + column and/or role + `_ck` + optional number. | `employees_salary_min_ck`, `orders_mode_ck` |
| **Index** | Constraint-serving indexes (PK/UK/FK) named after the constraint. Other indexes: table + columns (or purpose) + `_idx`. | |
| **Sequence** | Table name (or abbreviation) the sequence feeds, or its purpose, + `_seq`. | `employees_seq`, `order_number_seq` |
| **Function** | Verb + noun; name should answer "What is the outcome?" Don't prefix with `get_` (a function always gets something). Be more specific when several functions share an outcome. | `employee_by_id` |
| **Procedure** | Verb + noun; name should answer "What is done?" Use underscores between words (some editors uppercase the object tree). | `calculate_salary`, `set_hiredate`, `check_order_state` |
| **Package** | Named from the content it contains. | `employees_api` (API for the employee table), `logging_up` (utilities incl. logging) |
| **Collection type** | Name of collected objects + `_ct`. | `employees_ct`, `orders_ct` |
| **Object type** | Content (singular) + `_ot`. | `employee_ot` |
| **DML / Instead-of trigger** | Either object name + triggering events (`_br_iud` = Before Row Insert/Update/Delete, `_io_id` = Instead Of Insert/Delete), **or** object name + activity + `_trg`. | `employees_br_iud`, `orders_audit_trg`, `orders_journal_trg` |
| **System trigger** | Event name + activity done by the trigger + `_trg`. | `ddl_audit_trg`, `logon_trg` |
| **Synonym** | Use synonyms to address an object in a foreign schema, not to rename. Share the name with the referenced object. | |