G-9201
🆓Always follow naming conventions for tables.
Reason
SQL identifiers share the same namespace as PL/SQL identifiers. Follow naming conventions to prevent naming conflicts, improve readability, and clearly indicate the scope without forcing the use of qualified names. A common practice is to use a prefix and/or suffix to distinguish the identifier types.
Recommendations
Plural name of what is contained in the table (unless the table is designed to always hold one row only – then you should use a singular name).
Suffixed by _eb when protected by an editioning view.
Add a comment to the database dictionary for every table and every column in the table. See also G-1260 and G-1270.
Optionally prefixed by a project abbreviation.
Examples
employeesdepartmentscountries_eb- table interfaced by an editioning view namedcountriessct_contractssct_contract_linessct_incentive_modules
Why plural names?
We see a table and a view as a collection. A jar containing beans is labeled "beans". In Java we call such a collection also "beans" (List<Bean> beans) and name an entry "bean" (for (Bean bean : beans) {...}). An entry of a table is a row (singular) and a table can contain an unbounded number of rows (plural). This and the fact that the Oracle Database uses the same concept for their tables and views lead to the decision to use the plural to name a table or a view.
Example
Non-Compliant Example
create table départements ( deptno number -- ... );
Issues
| Line | Column | Message |
|---|---|---|
| 1 | 14 | table départements does not match '(?i)^[a-z0-9$#_]+'. |
We do not want accented letters in table names.
Compliant Solution - ★★★★★
create table departements ( deptno number -- ... );
All accented letters are replaced with plain Latin letters.
Tests
Test SQL query
select owner || '.' || table_name as identifier,
'Table ' || table_name || ' does not match '''
|| lower(#TablePattern#) || '''.' as message
from dba_tables t
where table_name not like 'AQ$%'
and temporary = 'N'
and iot_type is null
and owner in (#SchemaNames#)
and not regexp_like(table_name, replace(lower(#TablePattern#), '(?i)', null), 'i')
and not exists (
select 1
from dba_queue_tables qt
where qt.owner = t.owner
and qt.queue_table = t.table_name
)
and not exists (
select 1
from dba_recyclebin rb
where rb.owner = t.owner
and rb.object_name = t.table_name
)
order by owner, table_nameTest results
| Identifier | Message | Migration |
|---|---|---|
| DBL_OWNER.DÉPARTEMENTS | Table DÉPARTEMENTS does not match '(?i)^[a-z0-9$#_]+'. | - |
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| TablePattern | Regular expression pattern for SQL tables. | (?i)^[a-z][a-z0-9$#_]*$ |
| SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |