Difference between revisions of "Back-End Standard"

From SmartHCM Wiki
Jump to navigation Jump to search
 
(35 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 +
[[Category:Developer Guide]]
 +
 
== Naming Conventions and Standards ==
 
== Naming Conventions and Standards ==
  
−
*Database Object Name (i.e. Table, Sequence, View, Index, Trigger, Package, Procedure, Function, Materialized View, Job, Type, Directory, Constraints, Synonyms and Tablespace  ) should be meaningful. Example : Table Name "HRS_EMPLOYEE_PERSONNEL"
+
*Database Object Name (i.e. Table, Sequence, View, Index, Trigger, Package, Procedure, Function, Materialized View, Job, Type, Directory, Constraints, Synonyms and Tablespace  ) should be meaningful and should not be plural it should be singular like "Set_Location" not like "Set_Locations".
−
*Column Name should be clearly defined and meaningful. Example : Employee_ID
+
Example : Table Name "HRS_EMPLOYEE_PERSONNEL"
 +
*Column Name should be clearly defined and meaningful and should not be plural it should be singular like "Description" not like "Descriptions". Column name should represent the attribute meaningful name should not be same like the table name or object name.  
 +
Example : Employee_ID
 
*Database Object name and Column name should not contain the Number and Special characters except "_" character
 
*Database Object name and Column name should not contain the Number and Special characters except "_" character
 
*Constraints name should be clearly defined and must be used the prefix and suffix when creating the constraints
 
*Constraints name should be clearly defined and must be used the prefix and suffix when creating the constraints
Line 14: Line 18:
 
! Example
 
! Example
 
|-
 
|-
−
| fk
+
| fk<x>
 
| References (Foreign Key)
 
| References (Foreign Key)
−
| hr_emp_empid_fk
+
| hrs_employee_personnel_fk1
 
|-
 
|-
−
| uk
+
| uk<x>
 
| Unique Key
 
| Unique Key
−
| hr_emp_empid_uk
+
| hrs_employee_personnel_uk1
 
|-
 
|-
 
| pk
 
| pk
Line 26: Line 30:
 
| hr_emp_empid_pk
 
| hr_emp_empid_pk
 
|-
 
|-
−
| ck
+
| ck<x>
 
| Check
 
| Check
−
| hr_emp_empid_ck
+
| hrs_employee_personnel_ck1
 
|-
 
|-
−
| nn
+
| nn<x>
 
| Not Null
 
| Not Null
−
| hr_emp_empid_nn
+
| hrs_employee_personnel_nn1
 
|}
 
|}
 +
Note: Where "x" is the number.
  
 
*Use the following prefix in Identifier/Variable naming conventions (Local Variable, Global Variable, Parameter Variable, Constant Variable)
 
*Use the following prefix in Identifier/Variable naming conventions (Local Variable, Global Variable, Parameter Variable, Constant Variable)
Line 45: Line 50:
 
|-
 
|-
 
| k
 
| k
 +
| Constant
 +
| k_mailhost
 +
| Generic constant
 +
|-
 
| g
 
| g
 +
| Variable
 +
| g_loop_count
 +
| Global (package-level) variable
 +
|-
 
| v
 
| v
 +
| Variable
 +
| v_loop_count
 +
| Local variable
 +
|-
 
| p
 
| p
 +
| Variable
 +
| p_loop_count
 +
| Parameter variable
 +
|}
 +
 +
*Use the following prefix for scalar types that have aggregate data types.
 +
 +
{| class="wikitable" border="1"
 +
|-
 +
! Abbreviation
 +
! Description
 +
! Example
 +
|-
 +
| cur
 +
| Cursor
 +
| cur_employees
 +
|-
 +
| curp
 +
| Cursor parameter
 +
| curp_employee_id
 +
|-
 +
| r
 +
| Record
 +
| r_employee
 +
|}
 +
 +
*The suffix is used to qualify the identifier further by documenting its usage. For example, the suffix denotes the type of parameter, whether IN, OUT, or IN OUT, or to show that a variable or parameter is also a table.
 +
 +
{| class="wikitable" border="1"
 
|-
 
|-
−
| Constant
+
! Type
−
| Variable
+
! Description
−
| Variable
+
! Example
−
| Variable
+
|-
 +
|
 +
| Input-only parameter
 +
| p_num_items
 +
|-
 +
| out
 +
| Output-only parameter
 +
| p_sum_out
 +
|-
 +
| inout
 +
| Both input and output
 +
| p_sum_inout
 +
|}
 +
 
 +
===Product Naming Conventions and Standards===
 +
 
 +
*Use the following prefix in Table, Sequence, Package, Procedure, Function, Trigger, Materialized View , View, Index should be meaning full and start from Module name
 +
 
 +
{| class="wikitable" border="1"
 +
|-
 +
! Prefix
 +
! Module Name
 +
|-
 +
| SET
 +
| Settings
 +
|-
 +
| SEC
 +
| Security
 +
|-
 +
| WFL
 +
| Workflow
 +
|-
 +
| HRS
 +
|Human Resource System
 +
|-
 +
| PAY
 +
| Payroll
 +
|-
 +
| TOM
 +
| Time Office Management
 +
|-
 +
| OSP
 +
| Office Support Portal
 +
|-
 +
| ESS
 +
| Employee Self Service / Web Portal General Settings
 +
|-
 +
| COM
 +
| Communication System
 +
|-
 +
| MRR
 +
| Meeting Room Reservation
 +
|-
 +
| HRS_EREC
 +
| E-Recruitment
 +
|-
 +
| ARC
 +
| Correspondence System
 +
|-
 +
| INV
 +
| Inventory Module
 +
|-
 +
| FIN
 +
| Finance Module
 +
|-
 +
| PUR
 +
| Purchase Module
 +
|-
 +
| CON
 +
| Contract Module
 +
|-
 +
| DFS
 +
| Date Factory System
 +
|}
 +
 
 +
*Example Naming Standard of Database Objects for Table Specific
 +
 
 +
{| class="wikitable" border="1"
 +
|-
 +
! Objects
 +
! Module Prefix
 +
! Object Name
 +
! Object Type  Suffix
 +
! Example
 +
|-
 +
| Table
 +
| SET_
 +
| TABLE_NAME
 +
|
 +
| SET_LOCATION
 +
|-
 +
| View
 +
| SET_
 +
| TABLE_NAME
 +
| _VW
 +
| SET_LOCATION_LOC_ID_SEQ
 +
|-
 +
| Sequence
 +
| SET_
 +
| TABLE_NAME_COLUMN_NAME
 +
| _SEQ
 +
| SET_LOCATION_LOC_ID_SEQ
 +
|-
 +
| Package
 +
| SET_
 +
| TABLE_NAME
 +
| _PKG
 +
| SET_LOCATION_PKG
 +
|-
 +
| Procedure
 +
| SET_
 +
| TABLE_NAME
 +
| _PRO
 +
| SET_LOCATION_ PRO
 +
|-
 +
| Function
 +
| GET_
 +
| TABLE_NAME
 +
| ColumnName
 +
| GET_LOCATION_NAME
 +
|-
 +
| Trigger
 +
| SET_
 +
| TABLE_NAME
 +
| _TRG
 +
| SET_LOCATION_ TRG
 +
<_BU> (Before Update)
 +
<_BI> (Before Insert)
 +
<_AU> (After Update)
 +
<_AI> (After Insert)
 +
|-
 +
| Materialized View
 +
| SET_
 +
| TABLE_NAME_COLUMN_NAME
 +
| _MV
 +
| SET_LOCATION_ MV
 +
|-
 +
| Index
 +
| SET_
 +
| TABLE_NAME
 +
| _IDX
 +
| SET_LOCATION_LOC_ID_IDX
 +
|-
 +
| Constraints
 +
| SET_
 +
| TABLE_NAME_ ColumnName
 +
| _IDX
 +
| SET_LOCATION_LOC_ID_IDX
 +
|-
 +
| Constraints
 +
| SET_
 +
| TABLE_NAME_ ColumnName
 +
| _PK_UK_CK_NN_FK
 +
|SET_LOCATION_LOC_ID_PK, SET_LOCATION_LOC_ID_UK,SET_LOCATION_LOC_ID_CK,SET_LOCATION_LOC_ID_NN,SET_LOCATION_LOC_ID_FK
 +
|}
 +
 
 +
*Example Naming Standard of Database Objects for General use
 +
 
 +
{| class="wikitable" border="1"
 +
|-
 +
! Objects
 +
! Suffix
 +
! Abbreviation
 +
! Example
 +
|-
 +
| Package
 +
| _PKG
 +
| ModuleName / MeaningFullName ModuleName_Reports_PKG (For Reports) ModuleName_PKG (For general user for whole module)
 +
| HRS_LEAVE_PKG INV_REPORTS_PKG DFS_COMMON_PKG
 +
|-
 +
| Procedure
 +
| _PRO
 +
| ModuleName / MeaningFullName / Table Name
 +
| INV_ITEM_BALANCE_PRO
 +
|-
 +
| Function
 +
| GET_
 +
| ModuleName / MeaningFullName
 +
| GET_HIJRI_NEXT_MONTH
 +
|-
 +
| Trigger
 +
| TRG_
 +
| ModuleName / MeaningFullName,<_BU> (Before Update) <_BI> (Before Insert) <_BD> (Before Delete) <_BF> (Before insert, update, delete) <_AU> (After Update) <_AI> (After Insert) <_AD> (After Delete) <_AF> (After insert, update, delete) <_IU> (Instead of Update) <_II> (Instead of Insert)<_ID> (Instead of Delete) <_IN> (Instead of insert, update, delete)
 +
| TRG_LOCATION TRG_LOCATION_BF TRG_LOCATION_BI TRG_LOCATION_AU TRG_LOCATION_AI
 +
|-
 +
| Materialized View
 +
|
 +
| TableName
 +
|
 +
|-
 +
| Type
 +
| TYP_
 +
| MeaningFullName
 +
| TYP_GROSS_PAY
 +
|-
 +
|}
 +
 
 +
===Product Naming Conventions and Standards for Reports===
 +
*Naming Standard for "Report" Package
 +
 
 +
{| class="wikitable" border="1"
 
|-
 
|-
−
| k_mailhost
+
! Objects
−
| g_loop_count
+
! Suffix/Prefix
−
| v_loop_count
+
! Abbreviation
−
| p_loop_count
+
! Example
 
|-
 
|-
−
| Generic constant
+
|Package
−
| Global (package-level) variable
+
|_R<xxx>_PKG
−
| Local variable
+
|ModuleName_R<xxx>_PKG
−
| Parameter variable
+
|HRS_R001_PKG 
 +
INV_R001_PKG
 
|}
 
|}
 +
 +
==Good Programming practices==
 +
 +
There are several guidelines for SQL best practices, and some that apply directly to Oracle SQL.  In general, Oracle SQL best practices include these techniques
 +
 +
*Rewrite complex subqueries with temporary tables - Oracle create the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements.
 +
 +
*Use minus instead of EXISTS subqueries - Some say that using the MINUS operator instead of NOT IN and NOT Exists will result in a faster execution plan.
 +
 +
*Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.
 +
 +
*Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.
 +
 +
*Remove unnecessary large-table full-table scans - Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag-down an entire database and it's a SQL best practice to identify unnecessary  full scans and remove them by adding indexes.
 +
 +
*Cache small-table full-table scans - Caching is a SQL best practice in cases where a dedicated data buffer is available for caching table rows.
 +
 +
*Verify optimal index usage - This is one of the most important SQL best practices where you examine your SQL and verify that your SQL is using the most selective.
 +
 +
*Materialize your aggregations and summaries for static tables - One SQL best practice features of the Oracle 10g SQLAccess advisor is recommendations for new indexes and suggestions for materialized views.
 +
 +
 +
*The Basic format of Block Structure is as follows:
 +
 +
 +
DECLARE
 +
(Definition of any variable or objects that are used within the declared block)
 +
 +
BEGIN
 +
(Statement that make up the block)
 +
 +
EXCEPTION
 +
(All exception handlers)
 +
 +
END;
 +
(End of block marker)

Latest revision as of 08:00, 25 May 2015


Naming Conventions and Standards

  • Database Object Name (i.e. Table, Sequence, View, Index, Trigger, Package, Procedure, Function, Materialized View, Job, Type, Directory, Constraints, Synonyms and Tablespace ) should be meaningful and should not be plural it should be singular like "Set_Location" not like "Set_Locations".

Example : Table Name "HRS_EMPLOYEE_PERSONNEL"

  • Column Name should be clearly defined and meaningful and should not be plural it should be singular like "Description" not like "Descriptions". Column name should represent the attribute meaningful name should not be same like the table name or object name.

Example : Employee_ID

  • Database Object name and Column name should not contain the Number and Special characters except "_" character
  • Constraints name should be clearly defined and must be used the prefix and suffix when creating the constraints
Constraints Name
Abbreviation Description Example
fk<x> References (Foreign Key) hrs_employee_personnel_fk1
uk<x> Unique Key hrs_employee_personnel_uk1
pk Primary Key hr_emp_empid_pk
ck<x> Check hrs_employee_personnel_ck1
nn<x> Not Null hrs_employee_personnel_nn1

Note: Where "x" is the number.

  • Use the following prefix in Identifier/Variable naming conventions (Local Variable, Global Variable, Parameter Variable, Constant Variable)
Abbreviation Description Example Comment
k Constant k_mailhost Generic constant
g Variable g_loop_count Global (package-level) variable
v Variable v_loop_count Local variable
p Variable p_loop_count Parameter variable
  • Use the following prefix for scalar types that have aggregate data types.
Abbreviation Description Example
cur Cursor cur_employees
curp Cursor parameter curp_employee_id
r Record r_employee
  • The suffix is used to qualify the identifier further by documenting its usage. For example, the suffix denotes the type of parameter, whether IN, OUT, or IN OUT, or to show that a variable or parameter is also a table.
Type Description Example
Input-only parameter p_num_items
out Output-only parameter p_sum_out
inout Both input and output p_sum_inout

Product Naming Conventions and Standards

  • Use the following prefix in Table, Sequence, Package, Procedure, Function, Trigger, Materialized View , View, Index should be meaning full and start from Module name
Prefix Module Name
SET Settings
SEC Security
WFL Workflow
HRS Human Resource System
PAY Payroll
TOM Time Office Management
OSP Office Support Portal
ESS Employee Self Service / Web Portal General Settings
COM Communication System
MRR Meeting Room Reservation
HRS_EREC E-Recruitment
ARC Correspondence System
INV Inventory Module
FIN Finance Module
PUR Purchase Module
CON Contract Module
DFS Date Factory System
  • Example Naming Standard of Database Objects for Table Specific
Objects Module Prefix Object Name Object Type Suffix Example
Table SET_ TABLE_NAME SET_LOCATION
View SET_ TABLE_NAME _VW SET_LOCATION_LOC_ID_SEQ
Sequence SET_ TABLE_NAME_COLUMN_NAME _SEQ SET_LOCATION_LOC_ID_SEQ
Package SET_ TABLE_NAME _PKG SET_LOCATION_PKG
Procedure SET_ TABLE_NAME _PRO SET_LOCATION_ PRO
Function GET_ TABLE_NAME ColumnName GET_LOCATION_NAME
Trigger SET_ TABLE_NAME _TRG SET_LOCATION_ TRG

<_BU> (Before Update) <_BI> (Before Insert) <_AU> (After Update) <_AI> (After Insert)

Materialized View SET_ TABLE_NAME_COLUMN_NAME _MV SET_LOCATION_ MV
Index SET_ TABLE_NAME _IDX SET_LOCATION_LOC_ID_IDX
Constraints SET_ TABLE_NAME_ ColumnName _IDX SET_LOCATION_LOC_ID_IDX
Constraints SET_ TABLE_NAME_ ColumnName _PK_UK_CK_NN_FK SET_LOCATION_LOC_ID_PK, SET_LOCATION_LOC_ID_UK,SET_LOCATION_LOC_ID_CK,SET_LOCATION_LOC_ID_NN,SET_LOCATION_LOC_ID_FK
  • Example Naming Standard of Database Objects for General use
Objects Suffix Abbreviation Example
Package _PKG ModuleName / MeaningFullName ModuleName_Reports_PKG (For Reports) ModuleName_PKG (For general user for whole module) HRS_LEAVE_PKG INV_REPORTS_PKG DFS_COMMON_PKG
Procedure _PRO ModuleName / MeaningFullName / Table Name INV_ITEM_BALANCE_PRO
Function GET_ ModuleName / MeaningFullName GET_HIJRI_NEXT_MONTH
Trigger TRG_ ModuleName / MeaningFullName,<_BU> (Before Update) <_BI> (Before Insert) <_BD> (Before Delete) <_BF> (Before insert, update, delete) <_AU> (After Update) <_AI> (After Insert) <_AD> (After Delete) <_AF> (After insert, update, delete) <_IU> (Instead of Update) <_II> (Instead of Insert)<_ID> (Instead of Delete) <_IN> (Instead of insert, update, delete) TRG_LOCATION TRG_LOCATION_BF TRG_LOCATION_BI TRG_LOCATION_AU TRG_LOCATION_AI
Materialized View TableName
Type TYP_ MeaningFullName TYP_GROSS_PAY

Product Naming Conventions and Standards for Reports

  • Naming Standard for "Report" Package
Objects Suffix/Prefix Abbreviation Example
Package _R<xxx>_PKG ModuleName_R<xxx>_PKG HRS_R001_PKG

INV_R001_PKG

Good Programming practices

There are several guidelines for SQL best practices, and some that apply directly to Oracle SQL. In general, Oracle SQL best practices include these techniques

  • Rewrite complex subqueries with temporary tables - Oracle create the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements.
  • Use minus instead of EXISTS subqueries - Some say that using the MINUS operator instead of NOT IN and NOT Exists will result in a faster execution plan.
  • Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.
  • Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.
  • Remove unnecessary large-table full-table scans - Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag-down an entire database and it's a SQL best practice to identify unnecessary full scans and remove them by adding indexes.
  • Cache small-table full-table scans - Caching is a SQL best practice in cases where a dedicated data buffer is available for caching table rows.
  • Verify optimal index usage - This is one of the most important SQL best practices where you examine your SQL and verify that your SQL is using the most selective.
  • Materialize your aggregations and summaries for static tables - One SQL best practice features of the Oracle 10g SQLAccess advisor is recommendations for new indexes and suggestions for materialized views.


  • The Basic format of Block Structure is as follows:


DECLARE (Definition of any variable or objects that are used within the declared block)

BEGIN (Statement that make up the block)

EXCEPTION (All exception handlers)

END; (End of block marker)

Retrieved from "https://wiki.smarthcm.com/index.php?title=Back-End_Standard&oldid=469"