This article show simple coding standard that I use for coding. Simple rules clean code.
General guidelines
- Do not use names with a leading numeric character.
- Always choose meaningful and specific names.
- Avoid using abbreviations unless the full name is excessively long.
- Avoid long abbreviations. Abbreviations should be shorter than 5 characters.
- Any abbreviations must be widely known and accepted. Create a glossary with all accepted abbreviations.
- Do not use ORACLE reserved words as names. A list of ORACLE’s reserved words may be found in the dictionary view V$RESERVED_WORDS.
- Avoid adding redundant or meaningless prefixes and suffixes to identifiers.
Example: CREATE TABLE EMP_TABLE.
- Always use the same names for elements with the same meaning.
Database Object Naming Conventions
Never enclose object names (table names, column names, etc.) in double quotes to enforce mixed case or lower case object names in the data dictionary.
Identifier
|
Naming conventions
|
Column
|
- singular name of what is stored in the column unless the column data type is a collection, then you use a plural name
- add a comment to the database dictionary for every column
COMMENT ON COLUMN schema.table.column IS string;
|
Object Type
|
- name of an object type is built by its content (singular) followed by an “_obj” suffix.
Examples:
|
Collection Type
|
- collection type should include the name of the collected objects in their name.
- should have the suffix “_tab” to identify it as a collection.
Examples:
|
Primary Key Constraint
|
- table name or table abbreviation followed by the suffix “_pk”.
- optionally prefixed by a project abbreviation.
Examples:
|
Foreign Key Constraint
|
- table abbreviation followed by referenced table abbreviation followed by a “_fk” an and optional number suffix.
- optionally prefixed by a project abbreviation.
Examples:
- fact_orders_dim_prod_fk
- fact_invoices_dim_geo_fk
|
Function
|
- name is built from a verb followed by a noun. The name of the function should answer the question “What is the outcome of the function?”
- It should have prefix “fn_“Examples:
3. If more than one function provides the same outcome, you have to be more specific with the name.
Examples:
- fn_get_next_name
- fn_get_next_email
|
Index
|
- Indexes serving a constraint (primary, unique or foreign key) are named accordingly like constraints
- other indexes should have the name of the table and columns (or their purpose) in their name and should also have “_idx” as a suffix.
Examples:
- date_key_orders_ idx
- date_key_invoices_idx
3. bitmap indexes should use different suffix than b*tree indexes “_bidx”
Examples:
- product_key_invoices_bidx
- sales_category_orders_bidx
|
Package
|
- Name is built from the content that is contained within the package.
- Prefix “pkg_”
Examples:
- pkg_warehouse_processing
- pkg_oltp_processing
|
Procedure
|
- name is built from a verb followed by a noun. The name of the procedure should answer the question “What is done?”
- should use prefix “prc_”
Examples:
- prc_calculate_salary
- prc_set_hiredate
- prc_check_order_state
|
Sequence
|
- name is built from the table name (or its abbreviation) the sequence serves as primary key generator and the suffix “_seq” or the purpose of the sequence followed by a _seq.
Examples:
- migration_seq
- product_seq
|
Table
|
- 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
- optionally prefixed by a project abbreviation or type of table
- STG_ – stage tables
- EXT_ – external tables
- META_ – metadata tables
- FCT_ – fact tables
- DIM_ – dimension tables
- AGGR_ – aggregation tables
- CACHE_ – cache tables
- BCKP_ – backup tables
- add a comment to the database dictionary for every table.
COMMENT ON TABLE schema.table IS string; Examples:
- stg_geo_load
- dim_geo
- fact_order
- meta_setup
|
Temporary Table
|
- naming as described for tables.
- suffixed by “_tmp”
Examples:
|
Unique Key Constraint
|
- table name or table abbreviation followed by the role of the unique constraint, an “_uk” and an optional number suffix.
- optionally prefixed by a project abbreviation.
Examples:
|
Materialized View, View
|
- Plural name of what is contained in the view.
- Optionally prefixed by a project abbreviation.
- Optionally suffixed by an indicator identifying the object as a view
- (mostly used, when a 1:1 view layer lies above the table layer)
- Add a comment to the database dictionary for every view and every column.
- prefix “vw_” for views and “mv_” for materialized views
Examples:
- vw_active_products
- mv_sum_orders
|
Naming conventions for variables
Basic type of declarations are global, local and parameters
Base type of variable
|
Prefix
|
Naming conventions – examples
|
Global variable
|
g
|
Examples:
|
Local variable
|
l
|
Examples:
|
Parameter
|
p
|
Examples:
|
More advanced attributes that can be declared for variable in PL/SQL
Variable definitions
|
Suffix
|
Naming conventions – examples
|
Cursor
|
cur
|
Examples:
- g_name_cur
- l_name_cur
- p_name_cur
|
Record
|
rec
|
Examples:
- g_name_rec
- l_name_rec
- p_name_rec
|
Array/Table
|
tab
|
Examples:
- g_name_tab
- l_name_tab
- p_name_tab
|
Object
|
obj
|
Examples:
- g_name_obj
- l_name_obj
- p_name_obj
|
Constants
|
const
|
Examples:
- g_name_const
- l_name_const
|
Type definitions inside code is very special case. It’s not variable but new type that a variable can use to define its type.
Definition
|
prefix and suffix
|
Naming conventions
|
Type definition
|
type_ _rec,tab,obj
|
Exception it should have prefix type_ and suffix
Examples:
- type_name_rec
- type_name_tab
- type_name_obj
Please notice that types created on database level don’t have type_ prefix only suffix _obj or _tab
|
Have a fun
Tomasz