Oracle Database Application Design Rules
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Oracle Database Application Design Rules as PDF for free.
More details
- Words: 2,715
- Pages: 9
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__
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>_
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_
