Oracle Database Application Design Rules

  • November 2019
  • PDF

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