Database

We have set up the APEX environment, now let's focus on the database. For means of consistency and maintainability, it's a good practice to use standards and guidelines. In this section, we will describe some standards and guidelines for data modeling, database objects, and PL/SQL usage.

Data model

When the requirements are clear, we can create a data model. A data model provides the structure, definition, and format of the data.

We will translate the requirements into tables, columns, and relations. It will be the single point of truth of our system. Because our whole system is built upon this model, it's very important to spend sufficient time on it. A data model is also a great means to communicate with your customers and among developers about the system and design of the database. When a database is well designed, it can be easily maintained for future development.There are a number of Computer Aided Software Engineering (CASE) tools that you can use to create a data model. Besides data modeling, some of these CASE tools can also be used to maintain all the PL/SQL packages, functions, procedures, and trigger code that we use in our applications. Oracle itself provides Oracle Designer and Data Modeler from SQL Developer. The following diagram shows Data Modeler. One of the advantages of using such a tool is the ability to generate and reverse engineer the database creation scripts in an intuitive graphical manner.

SQL Developer Data Modeler

Relations between the tables are always in a one-to-many relationship; for example, a user can perform one or more searches. We can use colors to differentiate between tables that have a lot of mutations and tables that don't have any. Those tables can be candidates for creating lists of values (discussed later in this chapter).

A great example of standards and guidelines is Oracle's well-documented CDM RuleFrame. Database modeling standards and guidelines can be as follows:

Table names are written in plural.
Check constraints will be used for short domains on columns. If they are long or not know yet, we use a lookup table.
The primary key is always named as id. This is useful when we want to write reusable generic code.
For each table we define a short three- or four-letter alias.
Foreign key column names are constructed as follows:
1. The alias of the join table name is postfixed with id.
2. For every foreign key we define an index.
We use database triggers to populate the primary keys and use one sequence that will be used to populate all the primary keys. For the triggers, a script such as the following can be used for all tables:
```
CREATE OR REPLACE TRIGGER doc_bir 
BEFORE INSERT ON documents 
FOR EACH ROW
BEGIN
  :new_id := NVL(:new_id,all_seq.NEXTVAL);
END;
/
```
An alternative to triggers and sequence is the use of sys_guid(). On the Internet, a lot of information about the pros and cons for both approaches is available. Define all the id columns as the RAW(16) columns and use sys_guid() as a default value for the id column. For example:
```
CREATE TABLE t
  ( id       RAW(16) DEFAULT sys_guid() PRIMARY KEY
  , column_a VARCHAR2(10)
  , column_b VARCHAR2(10)
  …
  )
  /
```

Creating the database objects

The first thing we have to do is create the database schema, which will hold the database objects. We can use the SQL Workshop of APEX for creating the database objects, but its use is very limited compared to the specialized CASE tools.

The following objects can be created in the application schema:

Tables
Primary keys
Unique constraints
Foreign keys
Indexes on the foreign keys
Other indexes
Sequences
Scripts for insert and update triggers on the tables to generate an ID
Other objects (packages, materialized views, and so on)

Other tools

Beside the tools for creating a model, we need some tools during the further development process, tools for accessing the database easily, and tools for web development. Without going into detail, we will just name a few tools that you can use.

Examples of database tools are:

Toad
SQL Developer
PL/SQL Developer

Tools for web development, HTML, CSS, and JavaScript are as follows:

Aptana
Firebug
Web Developer
Internet Explorer Developer Tools
Built-in tools in the browser

Miscellaneous tools:

Versioning tools
Performance measurement tools
GUI design tools

Refer to Chapter 5, Debugging and Troubleshooting for the details on other tools.

PL/SQL usage

We use the following guidelines regarding PL/SQL:

Keep PL/SQL in APEX to an absolute minimum
Try to store all your PL/SQL in packages in the database
Replace PL/SQL blocks in APEX with simple calls to the functions and procedures in those packages.

This approach has the following advantages:

Easier to debug
Higher maintainability, due to more structure
Better reusability possible
Don't deploy an application each time there is a change in PL/SQL
Easier to tune

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Oracle APEX Best Practices

By : Learco Brizzi, Iloon Ellen-Wolff, Alexander Louis Leonard Nuijten

Oracle APEX Best Practices

By: Learco Brizzi, Iloon Ellen-Wolff, Alexander Louis Leonard Nuijten

Overview of this book

Database

Data model

Creating the database objects

Other tools

PL/SQL usage

Oracle APEX Best Practices

By : Learco Brizzi, Iloon Ellen-Wolff, Alexander Louis Leonard Nuijten

Oracle APEX Best Practices

By: Learco Brizzi, Iloon Ellen-Wolff, Alexander Louis Leonard Nuijten

Overview of this book

Database

Data model

Creating the database objects

Other tools

PL/SQL usage

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access