Database Reference
In-Depth Information
from moving it out to the database isn't worth the extra management overhead. As a
general rule of thumb, logic that controls or manipulates the UI is best placed in APEX,
and logic that implements business rules or controls the data is best placed in stored
program units in the database.
Placement of Database Objects
The Oracle database is very flexible, allowing data from multiple schemas to be gran-
ted to and queried by other schemas, even across database links. The APEX wizards
have been coded to work best when the database objects reside in a “parse as” schema
assigned directly to the workspace.
The APEX wizards make heavy use of database metadata for the objects in its
“parse as” schemas. If you're trying to create applications against synonyms from an-
other schema or across a database link to another database, in many cases the wizards
won't be functional because the metadata for these objects is unavailable. Some fea-
tures won't work at all, such as the management of BLOB data across database links.
In general, reports are much easier to deal with when it comes to disparate data, be-
cause you can supply a working query and create a report. Forms, however, become
much more difficult because the insert, update, and delete logic must be coded manu-
ally instead of relying on the APEX-supplied automated DML processes.
Although it's not always possible, the best practice is to create the underlying data-
base objects in the “parse as” schema for the application. This is how you will architect
your help-desk system.
Translating Theory to Practice
Now that you have a reasonable understanding of the things you need to think about
when designing the database objects for your system, you can translate your text-based
tables into a real schema definition. Although it's very easy to take the previously de-
scribed objects and attributes straight to SQL Workshop and start entering their defini-
tion, it's usually a good idea to go through the steps of creating an entity-relationship
diagram (ERD). Often, the action of doing this can bring other design considerations to
light.
There are dozens of ways to draw ERDs—from pen and paper to high-end database
design tools. However, we tend to take the middle ground and use Oracle's SQL
Developer Data Modeler, a robust and free tool from Oracle.
