ss

Oracle Tips by Burleson

Table-Naming Convention: An Example

Before a table-naming convention is established for a given project, the list of allowed abbreviations and acronyms should be compiled. This will prevent, for example developer A from naming the accounts payable table act_pay and developer B from naming the accounts receivable table accnt_rec.

According to the relational model, table names should always be a plural noun, since they are collections of entities (it logically follows that entity names should be singular in data models). Names should be as short as possible, between 8 and 16 characters. This length restriction is important, because, for column foreign key definitions and constraint naming, the table name is used in conjunction with column names in Designer and Developer products from Oracle.

For example, the table used to track ACCOUNTS PAYABLE in the PITS (Procurement Information Tracking System) application would be:

ACCT_PAY

assuming the standard abbreviation for ACCOUNT is ACCT and PAYABLE is PAY.

Wherever possible, abbreviations and acronyms should be used to shorten the table name. The TAB_COMMENTS and TAB_COL_COMMENTS tables can be used to store a more detailed name, which can be used in a simple help stored procedure to provide detailed information on the table and its columns. The COMMENT command is used to add comments to the DBA_TAB_COMMENTS and DBA_COL_COMMENTS views; it has the general format:

COMMENT ON TABLE|COLUMN [schema.]table|view|snapshot[.column] IS 'text';

The importance of short table names cannot be stressed enough. However, using codes like A1, B1, and Z28 for your tables is also to be avoided. Short meaningful names are the best.

A specialized type of table is the intersection table. An intersection table allows resolution of many-to-many and recursive relationships. Let’s say we have a many-to-many relationship between the PARTS table and the VENDORS table (VENDS for short). The resolving intersection table should be named:

VENDS_PARTS

(assuming the VENDORS table is the driving table for the relationship).

Creation of Simple Relational Tables: An Example

I define simple relational tables as those using standard relational structure, without the use of user-defined types (UDT), partitions, or special storage options such as index-only structure. In many applications the DBA works with, these will be the majority of tables involved.

See Code Depot