Design Rules
Page 1 of 9
Oracle Database Application Design Rules I. Introduction II. Oracle Layout
Development Environment Quality Assurance Environment Production Environment
III. Naming Standard
Oracle Database Creation Tablespace for application Datafile for tablespace Objects for Schema Tables Columns Constraints Indexes Views Temporary and Stage Tables Database Triggers Synonyms Passwords Database Links Sequences Program Files Application Security Schema IDs Roles
Appendix
Base Word Abbreviation List
I. Introduction The objective of this documentation is to provide information relevant to the dayto-day operations of the Oracle server and suggested best practices to follow when developing Oracle related applications. Standards for the creation and maintenance of relational databases should be as much a part of the infrastructure for software development as the computers and the database itself. Good standards do not constrain the creativity of designers and developers, but rather encourage the development of best practices. Also, as the name implies, an enterprise now has common and consistent practices that all members of the IT team can easily understand. If a standards document exists, then all contractors and employees have a reference for how to interpret existing
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
7/26/2006
Design Rules
Page 2 of 9 code and database objects. Even if the enterprise’s standards are different from an individual’s common practice, it still creates the consistent common environment. This can save hours of time and confusion and sometimes avoid costly mistakes. This document covers standards for Oracle database creation, objects for application and application security. This paper does not cover standards for design and coding (including SQL, PL/SQL, HTML, Java, etc.), GUI design, database and system administration (e.g. SID names, control file names, database file names, etc.), other file naming conventions and enterprise modeling rules. The intended audiences of this documentation are database administrators and application developers. Knowledge of Oracle is the prerequisite to understanding this document.
II. Oracle Layout Three kinds of database environments are recommended for application. These are:
Development Quality Assurance Production
Within any given database, there may be more than one application assigned. Operating policy will allow for applications to access and/or share their data within the same database, different databases, or even from different databases type. But access security must be followed Corporation standard and carefully designed.
Development Environment The Development environment is used for the development, maintenance, and enhancement and debugging of applications. It is also used for the initial installation, upgrades and testing of both system software and application software. This database environment will allow developers to access all database objects owned by the application. But application security MUST be implemented in this environment.
Quality Assurance Environment The Quality Assurance environment is used for acceptance testing by both enduser communities and operations. This database environment is controlled. Changes made to programs, databases objects and etc. will be approved, monitored by the appropriate groups and then be executed by Database Administrators. This database environment should be used to test and verify changes before implementation in the production environment.
Production Environment The Production environment is the most stable, restricted and controlled of all the database types. Changes made to this environment will be carried out by Database Administrators.
III. Naming Standard Oracle Database Creation Oracle software installation and database creation must be conducted by Database Administrators. Tablespace for application
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
7/26/2006
Design Rules
Page 3 of 9 An oracle database consists of one or more logical storage units called tablespaces which collectively store all of the database’s data. Each application will have its own tablespaces for data, indexes, text indexes and bitmap indexes. Tablespaces can be classified as follows:
Data Tablespaces with no partition. Data Tablespaces with partition. Data Tablespaces for Large Table General Index Tablespaces with no partition. General Index Tablespaces with partition. Index Tablespaces for large Index. Bitmap Index Tablespaces. Text Index Tablespaces.
The tablespace names should be the form of <Application name>_
___DATA/INDX Application name is abbreviation of Application name. (e.g. WA for world account) Function name is like SALES, PURCHASE etc. PRT is the partition type which depends on type of application. For the data mart type of application where we keep only fixed number of periods, the partitions will be the sequence number depending on the number of periods. For the data warehouse type of application, the partitions may be the partition year, or quarter or month. Tablespace structure and size should be done jointly by Database Administrators and Application Developers. Example: WA_SALES_DATA, WA_SALES_INDX, WA_SALES_PRT_2003_DATA Datafile for tablespace For performance reason, the data files of tablespace should spread out multiple spindles (LUNs). Especially, data files for index and data tablespace. The datafile name for tablespace should be the form of _,dbf, where nnn is the file sequence number for this tablespace. Example: WA_SALES_DATA_01.DBF, WA_SALES_INDX_01.DBF
Objects for Schema It is prohibited to use Oracle data dictionary naming conventions like V$, X$, USER_ and ALL_ for application object names. Use BASF standards for abbreviation. The following type of objects is the most commonly used. If other type of object is needed in your application, please contact Database Administration Service for naming standard. Tables Table names should be short but meaningful. Avoid generic meaningless table names like DETAIL_DATA, TMP3 or NEWSTUFF. Table names must be unique throughout an application and describe their full significance within the organization, not just within the application. It is a recommended practice to have columns to capture details of creation or modifications to a table. Below are the rules for table names:
Table names should be meaningful to business Table names should use underscores between words Each table should have comments Table storage definitions should be appropriate to anticipated table use; it is normal for storage clauses to be adjusted over time Every table should have a primary or unique key
Example: SALES.ORDER_HEADER, SALES.ORDER_LINE_ITEM (This means that these two tables belong to the SALES schema.)
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
7/26/2006
Design Rules
Page 4 of 9 Columns Column names should be short but meaningful and ended with the abbreviated base words. Avoid generic meaningless column names like COLUMN1_NO, or FIELD2_CD. Below are the rules for column names:
Column names must be unique within a table Use VARCHAR2 for text fields, even for columns of with a data length of 1 – VARCHAR2(1). The CHAR data type should not be used NUMBER columns should use correct precision, e.g. NUMBER(14).
Don’t use NUMBER without a precision. Specify the scale and precision of a fixed-point number column for extra integrity checking on input. Example: ORDER_NO, ORDER_ISSUE_DT Constraints Primary Key Constraints: Primary key constraint names should be the form of PK_. Each table should have a primary key – database will enforce with unique index (DDL to create key should specify the using index clause for index tablespace placement). Scripts to create primary key constraints should be created separately as ALTER TABLE scripts rather than in-line constraints. Example: PK_ORDER_HEADER Unique Key constraints: Unique key constraint names should be the form of UK__ where is sequence number for the unique key. Example: UK_ORDER_NO_01 Foreign Key Constraints: Foreign key constraint names should be the form of FK___ where is sequence number for the foreign keys. Use meaningful abbreviation for the table name. Example: The order number in the SALES.ORDER_LINE_ITEM table references the order number in the SALES.ORDER_HEADER table. The foreign key constraint should be named FK_ORD_LNE_ITEM_HEADER_01. Check Constraints: Check constraint names should be the form of CC__. Example: The SALES.ORDER_HEADER table has 3 check constraints that should be named CC_ORDER_HEADER_01, CC_ORDER_HEADER_02, and CC_ORDER_HEADER_03. Indexes Ordinary Indexes: Non-unique indexes should be the form of NDX_ _. Index storage definitions should be appropriate to anticipated table/index use; it is normal for storage clauses to be adjusted over time. Indexes should be created in a separate tablespace from the associated data. If an index name exceeds 30 characters by using its full table name, use meaningful abbreviation for the table name. Example: The SALES.ORDER_HEADER table has two ordinary non-unique
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
7/26/2006
Design Rules
Page 5 of 9 indexes that should be named NDX_ORDER_HEADER_01 and NDX_ORDER_HEADER_02. Partitioned Indexes: Partitioned indexes should be the form of the following: Index Type
Index Name
Partition Name
Primary Key
PK_
PRTPK__
Unique Key
UK__
PRTUK__
Ordinary Indexes NDX__ PRTNDX__ Bitmap Indexes
BMP__ PRTBMP__
The form of Global Index name can be treated the same as the Ordinary Index name. Example: The SALES.ORDER_HEADER table has two partitioned non-unique indexes that should be named PRTNDX_ORDER_HEADER_01 and PRTNDX_ORDER_HEADER_02. Views View names conform to the same general requirements as table names. Their names should reflect the function of the view, not the names of the underlying tables, and will be prefixed with V_ to distinguish them from tables. It will be usually be necessary to create a number of complex join views to enhance application and network performance. These views will always be accessed using the V_ prefix, in order to alert users to the fact that they are using a view. Below are the rules for the view names:
View names should be starting with V_ View names should use underscores between words Each view should have comments Avoid using recursive views within a view
Example: SALES.V_CANCELLED_ORDER is a joint view for the SALES.ORDER_HEADER and SALES.ORDER_LINE_ITEM tables. Temporary & Stage Tables Temporary tables are created and dropped for temporary usage by applications. Their names should be the form of TEMP_<meaningful name>. Stage tables are permanent tables that hold transient data. Their names should be the form of STG_<meaningful name>. It is expected that working tables be maintained by a given application which is responsible for populating and deleting rows. There can be significant system overhead on loading and unloading staging tables. Use TRUNCATE whenever possible instead of DELETE FROM. Temporary and stage table names should be prefixed by the application system acronym. Example: SALES.TEMP_ORDER_HEADER, SALES.STG_ORDER_LINE_ITEM Database Triggers Trigger names should be the form of TR___, where is an optional identifier starting at 1. Below are the trigger types for the database events: Before or After Statement (B or A)
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
7/26/2006
Design Rules
Page 6 of 9 Insert, Update or Delete (I, U or D) For each Row or for the statement (R or S) Avoid putting code directly in triggers unless it is very simple, i.e. limited to 10 lines or less. Use “packages instead” and call these from the trigger. Format: Before/After
Instead
Row/Statement
B or A
I,U,D
R or S
Therefore the trigger type would be one of the following: BIR, BIS, AIR, AIS, BUR, BUS, AUR, AUS, BDR, BDS, ADR, ADS. Example: TR_ORDER_HEADER_BUS_01 (This means that the trigger # 01 will be executed before the update occurs on the table SALES.ORDER_HEADER). Synonyms Public synonyms will not be allowed. Try to avoid using private synonyms. Passwords Password should not be encoded in the application. Follow BASF security policy at http://iwww.basf-corp.com/NTI_ComputerSecurity/index.html Database Links Database links will not be allowed from DEVL or QUAL to PROD. If necessary database administration should be contacted to refresh data from PROD to QUAL or DEVL instances. Public database links will not be allowed. When GLOBAL_NAMES = TRUE is set for the instance, the database link name must be the same as the connect string of the target database. When GLOBAL_NAMES = FALSE is set for the instance, the database link names should be the form of DBLNK__ where is sequence number of target database. Example: SALES_PROD is the database link to the SALES Production database when GLOBAL_NAMES = TRUE. DBLNK_SALES_PROD_01 is the database link to the SALES Production database when GLOBAL_NAMES = FALSE. Sequences For generic sequences: Sequences should be the form of SEQ_<Application Name>_nn where nn is the serial number. Example: Sales application has 3 generic sequences that should be named SEQ_SALES_01, SEQ_SALES_02, SEQ_SALES_03.
For specific sequences: Sequences should be the form of SEQ_<Application Name> Example: Sales application has 3 specific sequences on the attributes, ORDER_HEADER, ORDER_LINE_ITEM and ORDER_LOT that should be named SEQ_SALES_ORDER_HEADER, SEQ_SALES_ORDER_LINE_ITEM and SEQ_SALES_ORDER_LOT Program Files Application
File Type
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
File Extension
7/26/2006
Design Rules
Page 7 of 9
PLSQL
SQL Loader
SQLPLUS Procedures
.sql
Spooled files
.lst
Table definition source files
.tab
Constraint definition source files
.con
Index definition source files
.ind
Sequence definition source files
.seq
Views
.vw
Database procedure source files
.prc
Database trigger source files
.trg
Database packages source files
.pkg
Database packages definition source files
.pls
Database packages body source files
.plb
Control files
.ctl
Log files
.log
Bad data files
.bad
Input data files
.dat
Discard files
.dis
Application Security The following guildlines must be followed when designing an application.
Passwords of schemas in the quality and production environments will be maintained by Database Administration Group. Schema ids will not have connect, resource or DBA roles. It will have minimum privileges to keep the application running. End users will not be granted any system privilege, except create session privilege. End users will be granted the application roles. Schema ids must be included when database objects are accessed by application programs. Passwords should be not hard-coded in any application program.
Schema IDs Depending on the type of application (3 tier or 2 tier), the following example of type of schemas will be created. (Depending on the complexity of application, more IDs will be created.) Schema ID
DEVL
QUAL
PROD
Schema Name: It owns all objects of the application. (Example WA)
Developer can use this ID for creating objects
DBA’s will maintain the schema and objects
DBA’s will maintain the schema and objects
<Schema Name>_ADMIN: It is used by the person who supports/maintains the application.(Example WA_ADMIN)
Minimum Necessary privileges will be granted
Minimum Necessary privileges will be granted
Minimum Necessary privileges will be granted
<Schema Name>_BATCH_USER: It is used by batch job.
Minimum Necessary privileges will be granted
Minimum Necessary privileges will be granted
Minimum Necessary privileges will be granted
Minimum Necessary
Minimum Necessary
Minimum Necessary
<Schema Name>_USER
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
7/26/2006
Design Rules
Page 8 of 9 (Example WA_USER)
privileges will privileges will privileges will be granted be granted be granted Minimum Necessary privileges will be granted
End User (LAN ID)
Minimum Necessary privileges will be granted
Minimum Necessary privileges will be granted
Roles The application role should be created based upon the application function area. Role names should be the form of <Application Name>_ Example: SALES_UPDATE, SALES_READ_ONLY
Appendix Base Word Base Word Abbreviation AMOUNT
Meaning
amt
An amount of money. Must be accompanied by a currency code.
CODE
cd
A shortened symbolic representation of a meaning with multiple possible values. ALSO: an alphanumeric unique identifier.
COUNT
ct
Distinct physical or logical units of something. Not money or quantity.
DATE
dt
Full date including century, year, month, day (in any acceptable format); or a date component.
FACTOR
fct
Multiplier used to arrive at a standard defined measure.
IDENTIFIER
id
System-generated, internal alpha-numeric string used to uniquely identify a thing
INDICATOR
ind
Binary switch. Either on/off or yes/no. Blanks allowed.
NAME
nm
Words which are used to refer to an instance of something.
NUMBER
no
Numeric data other than money, quantity or price. ALSO: an alpha-numeric string which uniquely identifies an individual thing in common business language.
PERCENT
pct
A number expressed as its relationship to a whole.
PRICE
prc
Amount of money charged for each unit of an item. Must be accompanied by a currency code.
QUANTITY
qty
How much of something there is. The number of Units. Must be accompanied by a quantity unit of measure code.
RATE
rt
A quantity measured with respect to another measured quantity.
SIZE
sz
Dimensions of something.
TEXT
txt
Free form prose.
TIMESTAMP
ts
Full date and time including hours, minutes, seconds and fractional seconds.
VALUE
val
A numeric value which is not amount, factor, money, quantity, percent or rate. Often used to capture a constant.
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
7/26/2006
Design Rules
Page 9 of 9 WEIGHT
wt
A measure of the heaviness of something. Must be accompanied by a weight unit of measure code.
Abbreviation List theSource
Copyright © 1998-2003 BASF Corporation
http://iwww.basf-corp.com/NTI/R/DatabaseAdmin/design_rule.htm
7/26/2006
Related Documents