Database Coding and Naming Standards

Every Database table contain below security columns and they should NOT be NULL:

1. inserted_by
2. inserted_date
3. last_modified_by
4. last_modified_date

Naming Conventions:

Note: Any Oracle object name should not exceed 30 characters.

Sl#	Element	Naming convention	Example
1	Table	3 letter module short name + free form	Module: master Table : city Ex: Mst_city
2	views	3 letter module short name + free form + v	Mst_city_v
3	Materialized views	3 letter module short name + free form + mv	Mst_city_mv
4	Primary key	Short name of table + pk	Mst_city_pk
5	Foreign key	Short form of base table + short form of reffered table + fk	mst_city_mst_ctry_fk
6	Indexes	Short name of table + serial no + idx	mst_city_1_idx
7	sequences	Module short  name + free form + seq	Mst_emp_id_seq
8	Synonyms	Module name + free form + s	Mst_city_s
9	Public synonyms	Module name + free form + ps	Mst_city_ps
10	Db Link	Dbl_base db + source db	Dbl_user1_srcuser
11	Triggers	Trg + [when ][statement/row] + on action + table short  name Where when : B – before, A - after Statement/row: S /R action: I/ U / D , insert, update, delete	Trg_BS_I_mst_city
12	Package	PKG_free form name	PKG_travel_module
13	Procedure	Sp_free form name	sp_get_travel_details
14	functions	Fn_free form name	fn_get_currency_rate
15	parameters	Pi/po/pio + free form, where I – in, o – out, io - inout	pi_city_code
16	Local variables	lc_free form , where l – local, c - char	lv_cityname
17	Global varibales	gc_free form, where g – global, c - char	gn_min_age
18	Constant variables	cv_free form, where c – constant, v – carchar2	cn_max_salary
19	Cursor	Cur_free form	Cur_emp_list
20	Cursor variable	Curvar_free form	Curvar_emp_details
21	Ref cursor	Rc + free form	Rc_salary_details
22	Ref cursor variable	Lrcvar_free form	Lrcvar_salary_details
23	Type objects	Typ_free form	Typ_emp_details
24	Type object variables	lTypvar_free form, where l - local	ltypvar_emp_details
25	Table object type	Tab_free form	Tab_emp_list
26	Table object variable	lTabvar_free form, where l - local	ltabvar_emp_details
27
28

Coding Standards:

1. All the reserved words should be in upper case.
2. All table names should be in upper case.
3. Proper indentation should be followed across the code.
4. Proper comments should be given wherever possible.
5. Every script file should contain the following details in the header section:

5.1    script file name

5.2    script name

5.3    purpose

5.4    parameters

5.5    Author

5.6    Change History

...