Scribd
Upload
195 views

Naming Conventions For Oracle Tables

The document discusses the importance of following naming conventions when designing Oracle databases. It proposes conventions for naming tables, columns, indexes, sequences, views and other database objects. The key points are: - Names should be in uppercase and use letters, numbers and underscores only. Table names should be plural and include prefixes or suffixes for related objects. - Column names should identify the table they belong to. Foreign key columns should clearly show what table they reference. - Primary keys should be named with "_pk", foreign keys with "_fk", indexes with "_i", and sequences with "_seq" to easily identify the object type. - Views should be named like tables but prefixed with "vw_" and columns

Uploaded by

MANOJ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
195 views

Naming Conventions For Oracle Tables

The document discusses the importance of following naming conventions when designing Oracle databases. It proposes conventions for naming tables, columns, indexes, sequences, views and other database objects. The key points are: - Names should be in uppercase and use letters, numbers and underscores only. Table names should be plural and include prefixes or suffixes for related objects. - Column names should identify the table they belong to. Foreign key columns should clearly show what table they reference. - Primary keys should be named with "_pk", foreign keys with "_fk", indexes with "_i", and sequences with "_seq" to easily identify the object type. - Views should be named like tables but prefixed with "vw_" and columns

Uploaded by

MANOJ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

Naming conventions for Oracle tables, columns, indexes...

Why any naming conventions at all?


It is very important to follow some naming standards and guidelines in the process of
designing Oracle database. It is even more important if you have task to design Oracle
database containing hundreds of tables and thousands of other objects. If you have project
command of several people it is absolutely necessary to pick a naming convention , write
it down and USE it. Naming standards most probably won't affect database performance
although they will surely affect performance and level of understanding of designers,
developers and support staff. With consistent naming scheme they will understand data
model better and produce SQL statements faster. And this will directly affect your project
costs ;) 
I've used naming standards described below for all projects I've participated (and had
enough control). All of them were more or less OLTP projects although I couldn't see any
objection why not to use them in the data warehouse environment. They could probably be
used not only in Oracle but in other RDBMS'es as well (for example MySQL, PostgreSQL) but
you should check whether they haven't other widespread standards. 
See also my doc Do you have data waste or data base? for more general view how naming
conventions (or rather lack of them) can affect your data base. 

Common rules
1. 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
1. 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. 
Columns (for tables)
1. 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.

Some reasons why to use surrogate primary keys I


mentioned in Oracle-l list on freelists.org:
To my mind surrogate primary keys have at least
following benefits:
1) each table will have its own _id
2) they will be always the same type
3) so frontend as well as I in SQL*Plus screen can
use consistent
framework to access all tables in the SAME way
4) they wouldn't have any natural meaning and even
with the most
immutable natural keys in the world there is
possibility that they'll
change and I really don't want to change all
connected FK columns
5) I'd really don't want to write joins including
more than one column
for each pair because even for one pair developers
tend to create
Cartesian joins sometimes (once I had to make some
reports based on a
parent->child->grandchild->great-grandchild tables
joining 3 columns
for 2 tables - arrgh)
6) if you always need access parent (columns) from
great-great-..-grandchild you can easily add derived
FK's (or even
other columns i.e. make denormalization), but I'd say
these are
specific cases not ordinary needs. Of course these
(at least mostly)
have to be identified even BEFORE you generate any
CREATE TABLE
scripts.
To my mind UK's are just for natural keys and
potential waste of
properly cached sequence created surrogate key space
in table and
index is far outweighed by potential waste of 3
column FK, potential
change of it, nonconsistent access of tables both
from developer minds
and Oracle side.
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. 

Keys, Constraints
1. 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. 

Indexes
1. 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. 

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
1. 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. 

Columns (for views)


1. 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. 
Conclusion
The advantages are that one can surely identify object type and object relation with table
using queries to data dictionary, not use aliases in almost all selects (aliases must be in self
joins). 
Disadvantage - at least one - longer column names. 
One extra pro - Oracle Designer uses almost the same scheme. 

There must be at least some standard developing application and schema. Not
obligatory this one. So I think every approach is good enough if it isn't chaotic and
you are satisfied with it. 

Some further discussions about conventions mentioned above


In oracle technet: 
Schema Design Using Surrogate Key 
In google groups: 
Naming conventions for columns, primary and otherwise 
Why use a composite PK ever? 

Some more links


Oracle Naming conventions 
SQL Naming Conventions by Peter Gulutzan 
How To Write Unmaintainable Code 

References
I got some ideas from Joe Garrick's Database Programming Corner - Database Object
Naming Conventions. The link is now broken and I cannot find it any more. But it was
mostly for Access and SQL Server.

You might also like