The Application Developer
This chapter briefly outlines the steps involved in designing and implementing an Oracle database application. More detailed information needed to perform these tasks is provided later in this Guide. Although the specific tasks vary depending upon the type and complexity of the application being developed, in general the responsibilities of the application developer include the following:
This book is not meant to serve as a textbook on database or application design. If you are not already familiar with these areas, you should consult a text for guidance. Where appropriate, you are directed to other sections of this document for additional information.
Assessing Needs
The first step in designing a usable application is determining what problem you are trying to solve. It is important that you do not focus entirely on the data, but rather on how the data is being used. In designing your application you should try to answer the following questions:
- Who will be using this application?
- What are they trying to accomplish by using this application?
- How will they be accomplishing these tasks?
You should involve the end-user as much as possible early in the design phase. This helps eliminate problems that can stem from misunderstandings about the purpose of the application. After you gain a better understanding of the tasks that the end-users of the application are trying to perform, you can then determine the data that is necessary to complete these tasks. In this step, you need to look at each task and decide:
- What data must be available to perform this task?
- How must this data be processed?
- How can these results be meaningfully presented?
- What are the potential future uses of this application?
It is important that your audience has a clear understanding of your proposed solution. It is also important that your application be designed to accommodate the changing needs of your audience.
Designing the Database
At this point, you are ready to begin designing your data model. This model will allow you to determine how your data can be most efficiently stored and used. The Entity-Relationship model is often used to map a real-world system to a relational database management system.
The Entity-Relationship model categorizes all elements of a system as either an entity (a person, place, or thing) or a relationship between entities. Both constructs are represented by the same structure, a table. For example, in an order entry system, parts are entities, as are orders. Both part and order information is represented in tables. The relationship of which parts are requested by which order is also represented by a third table. The application of the Entity-Relationship model requires the following steps.
- First, identify the entities of your system and construct a table to represent each entity.
- Second, identify the relationships between the entities and either extend the current tables or create new tables to represent these relationships.
- Third, identify attributes of each entity and extend the tables to include these attributes.
When modeling a system with the Entity-Relationship model, you will often include a step called normalization. Textbooks on database design will tell you how to achieve Third Normal Form. Each table must have exactly one primary key and, in third normal form, all of the data in a table is dependent solely upon the table's primary key. You might find it necessary to violate normal form on occasion to achieve a desired performance level.
Proper application of the Entity-Relationship model results in well designed tables. The benefits of a set of well designed tables include the following:
- reduced storage of redundant data, which eliminates the cost of updating duplicates and avoids the risk of inconsistent results based on duplicates
- increased ability to effectively enforce integrity constraints
- increased ability to adapt to the growth and change of the system
- increased productivity based on the inherent flexibility of well designed relational systems
Oracle Corporation's products for database design, CASE*Dictionary, CASE*Method, and CASE*Designer, can help improve, automate, and document designs. See the CASE manuals for additional information.
After determining the overall structure of the tables in your database, you must next design the structure of these tables. This process involves selecting the proper datatype for each column and assigning each column a meaningful name. You can find information about selecting the appropriate Oracle datatype in Chapter 5 of this Guide.
If you are creating an application that runs on a distributed database, you must also determine where to locate this data and any links that are necessary to access the data across the network; see Oracle7 Server Distributed Systems, Volume I for additional information.
Designing the Application
After completing your database design, you are ready to begin designing the application itself. This, too, is an iterative process, and might also cause you to rethink your database design. As much as possible, you should involve your audience in these design decisions. You should make your application available to the end-users as early as possible in order for them to provide you with the feedback needed to fine tune your design.
There are many tools available, from Oracle Corporation as well as other vendors, to aid in the development and implementation of your application. Your first task is to evaluate the available tools and select those that are most appropriate.
Using Available Features
You must next determine how to implement your requirements using the features available in Oracle, as well as any other tools and utilities that you selected in the previous step. The features and tools that you choose to use to implement your application can significantly affect the performance of your application. The more effort you put into designing an efficient application, the less time you will have to spend tuning the application once it is complete.
Several of the more useful features available to Oracle application developers are listed below. Each of these topics is discussed in detail later in this book.
Integrity Constraints
Integrity constraints allow you to define certain requirements for the data that can be included in a table, and to ensure that these requirements are met regardless of how the data is entered. These constraints are included as part of the table definition, and require no programming to be enforced; see Chapter 6 for instructions on their use.
Stored Procedures and Packages
Commonly used procedures can be written once in PL/SQL and stored in the database for repeated use by applications. This ensures consistent behavior among applications, and can reduce your development and testing time.
Related procedures can be grouped into packages, which have a package specification separate from the package body. The package body can be altered and recompiled without affecting the package specification. This allows you to make changes to the package body that are not visible to end-users, and that do not require objects referencing the specification to be recompiled. See Chapter 7 for additional information.
Database Triggers
Complex business rules that cannot be enforced using declarative integrity constraints can be enforced using triggers. Triggers, which are similar to PL/SQL anonymous blocks, are automatically executed when a triggering statement is issued, regardless of the user or application. See Chapter 9 for additional information.
Database triggers can have such diverse uses as performing value-based auditing, maintaining derived data values, and enforcing complex security or integrity rules. By moving this code from your application into database triggers, you can ensure that all applications behave in a uniform manner.
Cost-Based Optimizer
The cost-based optimization method uses statistics about tables, along with information about the available indexes, to select an execution plan for SQL statements. This allows even inexperienced users to submit complex queries without having to worry about performance.
As an application designer, there may be times when you have knowledge of the data in your table that is not available to the optimizer, and that allows you to select a better execution path. In these cases, you can provide hints to the optimizer to allow it to select the proper execution path. See the Oracle7 Server Tuning manual for more information.
Shared SQL
Shared SQL allows multiple users to share a single runtime copy of procedures and SQL statements, significantly reducing memory requirements. If two identical SQL statements are issued, the shared SQL area used to process the first instance of the statement is reused for the processing of the subsequent instances of the same statement.
You should coordinate with your database administrator (DBA), as well as other application developers, to establish guidelines to ensure that statements and blocks that perform similar tasks can use the same shared SQL areas as often as possible. See your Oracle7 Server Tuning manual for additional information.
National Language Support
Oracle supports both single and multi-byte character encoding schemes. Because language-dependent data is stored separately from the code, you can easily add new languages and language-specific features (such as date formats) without altering your application code. Refer to the Oracle7 Server Reference manual for more information on national language support.
Locking
By default, Oracle provides row-level locking, allowing multiple users to access different rows of the same table without lock contention. Although this greatly reduces the chances of deadlocks occurring, you should still take care in designing your application to ensure that deadlocks do not occur.
Online transaction processing applications--that is, applications with multiple users concurrently modifying different rows of the same table--benefit the most from row-level locking. You should design your application with this feature in mind.
Oracle locks are also available to you for use within your applications. These locks are provided as part of the DBMS_LOCK package, which is described in Chapter 3.
Profiles
Profiles can be used to enforce per-query and per-session limits on resource use. When designing your applications, you might want to consider if any users have been denied access to the system due to limited resources. Profiles make it possible to allow these infrequent users limited access to the database. If you choose to allow access to these users, you must consider their requirements when formulating your design. Profiles are generally controlled by the database administrator. Consult your database administrator to determine if access can be granted to additional users and to identify this audience.
Sequences
You can use sequence numbers to automatically generate unique keys for your data, and to coordinate keys across multiple rows or tables. The sequence number generator eliminates the serialization caused by programmatically generating unique numbers by locking the most recently used value and then incrementing it. Sequence numbers can also be read from a sequence number cache, instead of disk, further increasing their speed.
Industry Standards Compliance
Oracle is designed to conform to industry standards. If your applications must conform to industry standards, you should consult the Oracle7 Server SQL Reference manual for a detailed explanation of Oracle's conformance to SQL standards.
Using the Oracle Call Interface
If you are developing applications that use the Oracle Call Interface (OCI), you should be aware that the OCI for Oracle7 release 7.3 offers many new calls. These provide
- new connection functionality
- the ability to insert and delete parts of a LONG or LONG RAW column, in addition to the previous capability to select pieces of these columns
- use arrays of C structs for bind and define operations
- a thread-safe library for OCI applications
Writing SQL
All operations performed on the information in an Oracle database are executed using SQL statements. After you have completed the design of your application, you need to begin designing the SQL statements that you will use to implement this design. You should have a thorough understanding of SQL before you begin to write your application. A general description of how SQL statements are executed is provided in Chapter 3 of this document. For more detailed information, see the Oracle7 Server SQL Reference manual.
You can significantly improve the performance of your application by tuning the SQL statements it uses. Tuning SQL statements is explained in detail in the Oracle7 Server Tuning manual.
Enforcing Security in Your Application
Your application design is not complete until you have determined the security requirements for the application. As part of your application design, you identified what tasks each user or group of users needed to perform. Now you must determine what privileges are required to perform these tasks. It is important to the security of the database that these users have no more access than is necessary to complete their tasks.
By having your application enable the appropriate roles when a user runs the application, you can ensure that the user can only access the database as you originally planned. Because roles are typically granted to users by the database administrator, you should coordinate with your database administrator to ensure that each user is granted access to the roles required by your application for a designated task. See Chapter 13 for more information on developing a security policy.
Tuning an Application
There are two important areas to think about when tuning your database application:
- tuning your SQL statements
- tuning your application design
Information on tuning your SQL statements, including how to use the cost-based optimization method, is included in the Oracle7 Server Tuning manual. Tuning your application design ideally occurs before you begin to implement your application. Before beginning your design, you should carefully read about each of the features described in this document and consider which features best suit your requirements. Some design decisions that you should consider are outlined below.
- Where possible, enforce business rules with integrity constraints rather than programmatically. See page 6 - 2 for a discussion of when to use integrity constraints.
- To improve performance, use PL/SQL. A description of how PL/SQL improves performance is included in the Oracle7 Server Tuning manual.
- Use packages to further improve performance and reduce runtime recompilations. Packages are described in Chapter 7.
- Use cached sequence numbers to generate primary key values; see page 4 - 29.
- Use VARCHAR2 to store character data instead of CHAR, which blank-pads data; see page 5 - 3.
- Store LONG and LONG RAW data in tables separate from related data and use referential integrity to relate them. This allows you to access the related data without having to read the LONG or LONG RAW data; see page 5 - 8.
- Use the SET_MODULE and SET_ACTION procedures in the DBMS_APPLICATION_INFO package to record the name of the executing module or transaction in the database for use later when tracking the performance of various modules. Registering the application allows system administrators and performance tuning specialists to track performance by module. System administrators can also use this information to track resource usage by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views. Registering applications is described in the Oracle7 Server Tuning manual.
You should also work with your database administrator to determine how the database can be tuned to accommodate your application. More detailed information on tuning your application, as well as information on database tuning, is included in the Oracle7 Server Tuning manual.
Maintaining and Updating an Application
If you are upgrading an existing application, or writing a new application to run on an existing database, you must follow many of the same procedures described earlier in this section. You must identify and understand the needs of your audience and design your application to accommodate them.
You must also work closely with the database administrator to determine
- what existing applications are available and how they are being used
- what data is available, if any can be eliminated, or if any additional data must be collected
- if any modifications must be made to the database structure, and how to make these changes in the least disruptive manner
