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

www.oracle-script.com |