Only letters, numbers, and the underscore are allowed in names. Although Oracle allows $ and #, they are not necessary and may cause unexpected problems.
2. All names are in UPPERCASE. Or at least of no importance which case. Ignoring this rule usually leads referencing to tables and columns very clumsy because all names must be included in double quotes.
3. The first character in the name must be letter.
4. Keep the names meaningful, but in the same time don't use long_names_describing_every_single_detail_of_particular_object.
Tables
Table names are in plural form, for example, persons, materials, addresses. If table name contains more than one word, they are separated with underscore in form {name1}_{name2}. Only the last one is in plural, for example person_addresses.
2. All tables have 3 or 4 character long aliases that are unique in a schema. Aliases are not directly used in name of table, but they are used to create column names. For example, persons - prs, materials - mat, addresses - adr.
3. Sometimes it is useful to distinguish some logical parts of an application. Therefore prefixes are used in table names. For example, sec_users, sec_roles, sec_rights all are related to security subsystem.
Table Names
Table names are plural, field name is singular
Table names should not contain spaces, should be split_up_with_underscores. Limit the table name to 23 characters.
If the table name contains serveral words, only the last one should be plural:
APPLICATIONS
APPLICATION_FUNCTIONS
APPLICATION_FUNCTION_ROLES
It is a bad idea to use a prefix or suffix to identify tables. Suppose, your naming convention is to have the '_TAB' suffix for all tables. According to that naming convention, the APPLICATIONS table would be called APPLICATIONS_TAB. If as time goes by, your application gets a second login, perhaps for auditing, or for security reasons. To avoid code changes, you will then have to create a View or Synonym that points at the original tables and is called APPLICATIONS_TAB. Confusing at best.
Columns (for tables)
All columns are in form {alias}_{colname}. For example prs_id, prs_name, prs_adr_id, adr_street_name. This guarantees that column names are unique in a schema, except denormalized columns from another table, which are populated using triggers or application logic.
2. All columns are in singular. If you think you need a column name in plural think twice whether it is the right design? Usually it means you are including multiple values in the same column and that should be avoided.
3. All tables have surrogate primary key column in form {alias}_id, which is the first column in the table. For example, prs_id, mat_id, adr_id.
4. All foreign key columns are in form {alias1}_{alias2}_id. For example, prs_adr_id. Try to put all foreign key columns just after the primary key column, mostly because of human factor. The first glance gives nice overview how many and which tables are referenced. Of course as time goes by, schema evolves and columns are added situation may change.
5. If there is more than one foreign key column to another table then foreign key columns are named {alias1}_{alias2}_{meaningful_name}_id. For example, prs_adr_curr_id, prs_adr_prev_id.
6. If several tables contain columns with the same content use the same consistent column names. For example if you need some auditing info, then use {alias}_last_chg_time and {alias}_last_chg_user for all tables. Of course, you can choose your own column names but use them consistently. It is also relevant for some flag columns, for example {alias}_is_active and also columns containing describing info - use either notes, description, comments or whatever but only one of them for the same purpose. All consistencies help to understand the data model, all inconsistencies - prevent.
Column Suffixes
Use the following standard suffixes for database column names:
_ID Surrogate keys
_SEQ Sequence numbers
_FLAG Yes/No QuickCode columns
_CODE Other QuickCode columns
_NAME Spelled-out, user-visible columns
_NUM User-supplied primary keys that are numbers
_PERCENT Percentages, which are normally in the range 0 to 100
Field Names
Fields should be unique within the database schema.
The convention is to prefix the fieldname with a 2 or 3 character contraction of the table name e.g.
PATIENT_OPTIONS would have a field called po_patient_option
PATIENT_RELATIVESwould have a field called pr_relative_name
ABSENCES would have a field called ab_start_date
In a large schema you will often find two tables having similar names which could result in the same prefix. You can avoid this by thinking carefully about the name you give each table - and documenting the prefixes to be used. This is a pain initially but the benefits are well worth it.
For very complex systems (thousands of tables) consider alternatives e.g. a prefix/suffix to identify the Application module.
Keeping names short: Oracle places no limit on the number of columns in a GROUP BY clause or the number of sort specifications in an ORDER BY clause. However, the sum of the sizes of all such expressions is limited to the size of an Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.
Keys, Constraints
All primary keys are named {alias}_pk. For example prs_pk.
2. All foreign keys are named {alias1}_{alias2}_fk. For example prs_adr_fk.
3. If there are more than one foreign key to another table then foreign keys are named {alias1}_{alias2}_{meaningful_name}_fk. For example, prs_adr_curr_fk, prs_adr_prev_fk.
4. All unique keys are named {alias}_{meaningful_name}_uk. For example, mat_typename_uk which is on columns mat_name and mat_type.
5. All check constraints are named {alias}_{meaningful_name|number}_ck. For example, mat_type_ck which is on column mat_type.
Constraints
Primary and Unique constraints will be explicitly named.
Name the Primary Key Constraint as pk_
e.g.
PATIENTS would have a primary key index called pk_patients
Name a Foreign Key Constraint as fk_
e.g.
PATIENTS would have a Foreign Key constraint called fk_patients
Note - in general each constraint should have a similar name to the index used to support the constraint.
Primary Key Fields - indicate by appending _pk
e.g.
PATIENTS would have a primary key called pa_patient_id_pk
REGIONS would have a primary key called re_region_id_pk
And so on...the name of the primary key field being a singular version of the table name. Other tables containing this as a foreign key would omit the _PK
so
CLINIC_ATTENDANCE might then have a foreign key called ca_patient_id
or alternatively: ca_patient_id_fk
Tables with Compound PK's, use _ck in place of _pk
Notice that where several tables use the same PK as part of a compound foreign key then the only unique part of the FK fieldname will be the table prefix.
Indexes
Every index on foreign key is in form {alias1}_{alias2}_fk_i. For example, prs_adr_fk_i.
2. Every index on one column is in form {full_column_name}_i. For example, mat_name_i.
3. Every index on two or more columns is in form {alias}_{meaningful_name|number}_i. For example, mat_1_i, mat_2_i.
Index names
Name the Primary Key index as idx_
e.g.
PATIENTS would have a primary key index called idx_patients_pk
Name a Unique Index as idx_
e.g.
PATIENTS would have a unique index called idx_patients_uk
Where more indexes are added to the same table, simply append a numeric:
idx_
Where ## is a simple number
e.g.
PATIENTS would have additional indexes called idx_patients_01, idx_patients_02,...
Note - Conventions that attempt to use the column name as part of the index name become unmanageable as soon as you have multiple columns appearing in multiple indexes.
Sequences
1. Every table has its own sequence in form {alias}_seq. It and ONLY it is used to populate corresponding primary key column. If an application needs some precreated data, remember to create sequences with start value greater than max value of corresponding column. For example, prs_seq, mat_seq.
Views
View names are prefixed with vw_.
2. View names are in plural form, for example, vw_students. If view name contains more than one word, they are separated with underscore in form vw_{name1}_{name2}. Only the last one is in plural, for example vw_student_addresses.
3. All views have 3 or 4 character long aliases that are unique in a schema. It is necessary only in case the view contains some complex derived columns from underlying tables.
View Names
View names are plural, field name is singular
View names should not contain spaces, should be split_up_with_underscores and prefixed with 'vw_' This ensures the view name remains readable even where case is not preserved. (A common alternative is to suffix with _v)
vw_your_view
your_view_v
Columns (for views)
All column names derived directly from tables stay the same. For example column prs_name in view vw_students derived from table persons. Of course if view is a self join of two tables then you have to make view column names unique, for example prs_parent_name and prs_child_name in view vw_parents derived from self join of persons.
2. All column names that aren't directly derived from underlying tables e.g. summary columns, calculated columns etc. are in form {view_alias}_{colname}. For example column sta_complete_adress (concatenation of adr_country, adr_city and adr_street) for view vw_student_addresses.
Other Fields
Without getting carried away, you can also apply a suffix to non key fields where this is helpful in describing the type of data being stored.
e.g.
A number field used to store boolean (Yes/No) values can be named as:
In lookup tables an easy way to identify the main text field is to name it as a singular version of the tablename
e.g.
asset_types.at_asset_type_id_pk (Primary Key)
asset_types.at_asset_type (Text field)
asset_types.at_network_yn (boolean)
PL/SQL
Prefix scalar variable names with v_
Prefix global variables (including host or bind variables) with g_
Prefix constants with c_
Prefix procedure or function call parameters (including sql*plus substitution parameters) with p_
Prefix record collections with r_ (alternatively suffix with _record)
Prefix %rowtype% collections with rt_ (alternatively suffix with _record_type)
Prefix pl/sql tables with t_ (alternatively suffix with _table)
Prefix table types with tt_ (alternatively suffix with _table_type)
Suffix cursors with _cursor
Prefix exceptions with e_
If a pl/sql variable is identical to the name of a column in the table Oracle will interpret the name as a column name.
Instance
Oracle database instance names are limited to eight characters. The last two characters of the name should reflect the nature of the database.
e.g.
Live instance HLTa
Test instance HLTb
Train instance HLTc
Data Warehouse HLTdw
Staging Area HLTsa
Data Files
Name Data files so that they identify the instance and the tablespace.
Each filename should end with a two digit numeric value starting with 01, that is incremented by 1 for each new datafile added to the tablespace.
Use the extension ".dbf"
e.g.
HLTa_temp01.dbf
HLTa_rbs01.dbf
HLTa_clinical01.dbf
HLTa_clinical02.dbf
Tablespaces
Avoid naming tablespaces according to time periods.
(Oracle never forgets a tablespace and SMON will scan the list of tablespaces in TS$ every 5 minutes) For a partitioned datawarehouse, try to adopt a strategy of recycling the tablespace names.
Redo Logs
The redo log is a separate file (not in the tablespace)
Name Redo Logs so that they identify the instance, group and member number of the log. Use the extension ".log"
e.g.
HLTa_redo_01.log
For more detail on the physical placement of files see Oracle OFA
Documentation
Lastly - write and maintain a data dictionary for all data elements - rather than just dumping the Oracle data dictionary into a drawing or text document - you should be defining the business meaning of each data item.
Summary
RDBMS naming conventions can become the subject of endless debate - here are a few last things to consider:
Does your naming convention make names longer or shorter?
PURCHASE_ORDER_DATE versus PO_DATE
Will you have novice users writing SQL against the database?
If so will they understand the meaning of things like PO_DATE
Is the naming convention documented somewhere that everyone can find?
Oracle naming standards tips
Oracle Tips by Burleson Consulting
This document describes a standard naming convention for Oracle schema objects including table naming standards, index naming standards, constraint naming standards and column naming standards.
Oracle DBA's must pay careful attention to the structure and naming conventions and naming standards within the database. All applications will reside within the same schema owner and naming conventions will be used to identify table/index components:
Oracle Schema Naming Standards
The following standards will be used in all schemas:
Schema objects - All non-table schema objects will be prefixed by their type and all index names will begin with idx, and all constraint names will begin with cons.
Referential Integrity conventions - All tables will have full RI, including PK constraints, FK constraints and check constraints. The default for foreign key constraints will be "On Delete Restrict", unless otherwise specified. This means that no parent record can be deleted if there are corresponding child records.
Primary keys - Oracle Sequences will be used to generate unique row identifiers and all sequence numbers generally will start at one and increment by one.
Check Constraints - Lists of valid values will be used in all cases to restrict column values and validity
Oracle table naming Standards
To simplify development, we will follow these rules that allow the developer to quickly identify each metadata object, with complete descriptive names:
Table Standards
All table names will be plural (e.g. users vs. user).
Full table names will be used whenever possible.
If a table name should exceed 30 characters, reduce the size of the table name in this order:
From the left of the table name, remove vowels from each word in the table name except for the first vowel of each word.
If the table name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.
Oracle column naming Standards
Column Naming Standards
Column names should be spelled out whenever possible.
If a column name should exceed 30 characters, reduce the size of the column name in this order:
From the left of the column name, remove vowels from each word in the table name except for the first vowel of each word.
If the column name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.
Oracle index naming Standards
Index Standards
Index names should follow this standard:
IDX_ttttt_nn
Where
IDX = Index
tttt = Table name the index is built on
nn = Numeric value that makes each table index unique.
If an index name should exceed 30 characters, reduce the size of the index name in this order:
No comments:
Post a Comment