|
Oracle Naming Conventions
- Applications
- Applications will be identified by a name and a 2 or 3-letter code (APP in what follows).
- If a database contains several distinct applications, the name of all objects, which are unambiguously part
of the application, will be prefixed by the application code APP_ .
- User-defined Types
- User-defined types will be named [APP_]Name_t.
- Partitions
- Partitions will be named Name_Pn, where Name is the name of the partitioned table or index
and n is a number to uniquely identify the partition. Where a table is partitioned by year, n is the year.
- Tables and table columns
- Tables will be named [APP_]Name, with Name at most 24 characters long. References to TableName below implicitly include the application code prefix if it is used
- Table names will always be plural.
- Table names should not contain spaces, should be split_up_with_underscores. Only alphanumeric characters and underscores ‘_’ will be used (no #, no $) in 'base names'.
- Column names names will be singular or plural depending on the meaning for a single row. Examples : EmpName, Salary but Children or HoursWorked
- Column names will be at most 30 characters long
- Do not prefix column names.
- It is highly recommended, though not required, that each table have a primary key defined
- It is highly recommended, though not required, that primary key columns be suffixed with _id
- The first column(s) of a table will be required to be the primary key column(s), if a primary key is defined. This will allow better identification of the primary keys for a table.
- The foreign key column(s) of a table need to immediately follow the primary key columns of the table, if a foreign key(s) is defined. This will only be applicable when the table does not exist in production database.
- Indexes
- NONUNIQUE Indexes will be the table name suffixed with _nn such as table_name_n1
- UNIQUE Indexes will be the table name suffixed with _un such as table_name_u1
- BITMAPPED Indexes will be the table name suffixed with _bn such as table_name_b1
- FUNCTION based Indexes will be the table name suffixed with _fn such as table_name_f1
- See Also: 6. Integrity Constraints
- Integrity Constraints
- All integrity constraints except NOT NULL constraints must be explicitly named
- Primary Keys
- Primary Keys will be the table name suffixed with _pk such as table_name_pk
- Foreign Keys
- Foreign Keys will be the table name suffixed with _fkn where n is a number used to
uniquely identify the foreign key constraint in case of multiple foreign keys. Example: table_name would have a
foreign key table_name_fk1.
- All foreign keys are required to have an index built on them. The index should follow the standard naming conventions listed under indexes.
- Unique Keys
- Unique Keys will be the table name suffixed with _uk.
- Other Constraints
- CHECK constraints will be named TableName_ColumnName_ck12:13 PM 1/18/2005
- Naming NOT NULL constraints is not required or recommended.
- Views
- Views will be named [APP_]Name, where Name follows the same naming rules as tables.
- Synonyms
- Synonyms will be exclusively used to hide the fact that an object belongs to another schema. They will therefore take the same name as the object they are a synonym for.
- Sequences
- Sequences which are directly used as such to generate a primary key value will be named TableName_ID_S
- Sequences which are used to partially generate a value or for which there is a type conversion, will be
named TableName_ColumnName#[_p]_S where p is an optional sequence number to uniquely identify several sequences which would be used to generate a single column.
- Triggers
- Triggers will be named TableName_Tn<E><W> where E is the event which fires the trigger
(U - Update, I - Insert, or D - Delete). W is When the trigger is fired (B - Before, or A -
After). 'n' is the number of the trigger (1,2,3,...). The trigger that fires when a table is updated, before the
update occurs would be named table_name_T1_UB
- Packages, Functions, and Procedures
- Packages will be named [APP_][Module_]Name_pkg
- Stand-alone functions will be named [APP_][Module_]Name
- Functions in packages will be named Name. The optional application and module indication will be indicated at the package level only.
- Stand-alone procedures will be named [APP_][Module_]Name
- Procedures in packages will be named Name. The optional application and module indication will be indicated at the package level only.
- Recommendation: Packages should be used whenever possible.
- Database Links
- Database links will bear the name of the application which ‘owns’ the tables to be accessed. Connection will be done using an account whose name is the name of the current application.
Reference:
The naming conventions above provide a number of benefits. Many of these are more to do with human factors than any system limitations. There is no universal truth in the matter of naming standards. The only thing that matters is to define a consistent set of standards and then, when they have been accepted, to stubbornly stick to them. As long as they have not been implemented, the above rules can be amended at will.
Other notes:
- DATATYPES: Always use VARCHAR2 when defining character fields unless there is justification for using CHAR. Refer to the following points found in Oracle8i Application Developer's Guide - Fundamentals:
When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:
- Space Usage
To store data more efficiently, use the VARCHAR2 datatype. The CHAR datatype blank-pads and stores trailing blanks up to a fixed column length for all column values, while the VARCHAR2 datatype does not blank-pad or store trailing blanks for column values.
- Comparison Semantics
Use the CHAR datatype when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the VARCHAR2 when trailing blanks are important in string comparisons.
- Future Compatibility
The CHAR and VARCHAR2 datatypes are and will always be fully supported. At this time, the VARCHAR datatype automatically corresponds to the VARCHAR2 datatype and is reserved for future use.
- ABBREVIATIONS: When creating database indexes, foreign keys and other objects that are based on the original table name, there are times that the name becomes too long. To deal with this, our standard is to remove the vowels from the last word in the object name. For example for a table named POS_SUB_PROGRAM_REQUIREMENT adding on an _FK makes the name too long. As a result the name of the FK object is made POS_SUB_PROGRAM_RQRMNT_FK2. The vowels in the word requirement were removed.
UNI Database Applications
| Data Owner |
Database |
Schema |
Short Name |
Description |
| |
RPT |
webfocus_local |
RES |
Residence Webfocus Reporting |
| |
WEB |
|
ACCESS |
Consolidation of users tracking |
| |
WEB |
|
ADM |
Admissions - application for undergraduate admission, orientation sign-up, preview day, etc |
| |
WEB |
|
AD_ASTRA |
Ad Astra scheduling users |
| |
WEB |
|
APL |
Generic online applications - continuing ed |
| |
WEB |
|
ATH |
OLD USA Wrestling Tournament support for NCAA several years ago |
| |
WEB |
|
BBS |
OLD Browser-based survey |
| |
WEB |
|
CCD |
Centralized Course Database |
| |
WEB |
|
CMS |
Content Management System |
| |
WEB |
|
CUR |
Curriculum Change |
| |
WEB |
|
DA |
Degree Audit |
| |
WEB |
|
DG |
OLD data gather (not used) |
| |
WEB |
|
DIS |
Disaster Volunteer |
| |
WEB |
|
FA |
Financial Aid forms |
| |
WEB |
|
FINAID |
Financial Aid estimation calculator |
| |
WEB |
|
GEN |
General upload file storage |
| |
WEB |
|
INQ |
Inquiry (Admissions - first contact with a student requesting info) |
| |
WEB |
|
MFIS |
MEMFIS auth for sandbox training |
| |
WEB |
|
MF |
OLD MEMFIS training logs |
| |
WEB |
|
NAV |
Norton Antivirus download tracking |
| |
WEB |
|
NCCCC |
National Coalition for Campus Children Center membership |
| |
WEB |
|
OAPP |
MEMFIS Orgs |
| |
WEB |
|
OJB |
Online Job Board |
| |
WEB |
|
OSFE |
Office of Student Field Experience |
| |
WEB |
|
PAS |
Performance Assessment System |
| |
WEB |
|
PAWS |
Personalized Admissions Website |
| |
WEB |
|
PB |
Phonebook |
| |
WEB |
|
PLAN |
This is a plan table used to store query plans for SQL |
| |
WEB |
|
PLS |
Price Lab School |
| |
WEB |
|
PL |
Proxy Login (Parent Portal) |
| |
WEB |
|
PM |
Portal Messaging |
| |
WEB |
|
PORT |
Portal |
| |
WEB |
|
POS |
Plan of Study |
| |
WEB |
|
PP |
Physical Plant (buildings and rooms) |
| |
WEB |
|
PR |
Public Relations (Deans and graduation list) |
| |
WEB |
|
REG |
Grades for OLD people converted from old s ystem |
| |
WEB |
|
REQ |
Hypothetical degree audit |
| |
WEB |
|
RL |
Roles |
| |
WEB |
|
RPT |
Reports (grades, week at-a-glance, etc.) |
| |
WEB |
|
RS |
Department of Residence Application including University Apartments Applications |
| |
WEB |
|
SAR |
Student Academic Request |
| |
WEB |
|
SHOP |
Used for online shopping store tables (new system being created) |
| |
WEB |
|
STU |
Student Records |
| |
WEB |
|
SUB |
Sub Session Codes |
| |
WEB |
|
SUR |
Survey (new version, replaced BBS) |
| |
WEB |
|
SWS |
Software Sales |
| |
WEB |
|
TEA |
Teacher Education Application |
| |
WEB |
|
TEMP |
NOT SURE- mainfraime account code to MEMFIS org ID conversion |
| |
WEB |
|
TE |
Endorsements for Grade Report |
| |
WEB |
|
TOAD |
These are for the TOAD plan tables for storing query plans for SQL |
| |
WEB |
|
TPI |
Transfer Plan It |
| |
WEB |
|
TR |
Training |
| |
WEB |
|
UNIALERT |
UNI Alert |
| |
WEB |
|
UPI |
Update Personal Information |
| |
WEB |
|
WEBCT |
Used to assist with getting WebCT adds, drops, and enrollments |
| |
WEB |
|
WF |
PAR (workflow) |
| |
WEB |
|
WRS |
Wellness and Recreation Services (towel, user registration) |
| Advancement |
ADV |
multiple |
N/A |
Ruffalo Cody System |
| Library |
WEB |
LIB_PRD_D |
FRS |
Currently under development. |
| Help Desk |
WEB |
HEAT |
N/A |
Currently testing |
Change Log:
| Date |
Description |
| October 2002 |
Created |
| December 23, 2002 |
*Changed table name length specs from 26 to 24 characters
*Added recommendation for PK columns in tables.
*Changed Sequence Name requirements.
*Added guidelines on making object names shorter. |
|