Database Reference
In-Depth Information
If you take the example of the TICKET table, the ID for a ticket is an arbitrary piece
of data used only to uniquely identify one ticket from another. Therefore, it easily fits
into the realm of a surrogate primary key. Even if the spreadsheet that the help-desk
technicians currently use has IDs assigned to the tickets, you can load those values and
start your sequence counting at a point above the highest current TICKET ID. The
same is true for TICKET DETAILS. Even in the USER table, where you have a unique
single-column natural key (the User Name), it behooves you to implement a surrogate
key to be able to take advantage of the built-in APEX code paths.
Business Logic vs. User Interface Logic
Because it's written in PL/SQL, APEX takes full advantage of everything that PL/SQL
has to offer. The APEX development team has made thorough use of stored PL/SQL
program units for their business logic, and you can take a very important lesson from
them.
Although it's arguably a valid development method to prototype your business logic
by first coding it as an anonymous PL/SQL block inside of APEX, it's foolish to leave
it there long term. By moving it out into stored program units, you gain in many differ-
ent ways.
One very important gain is in the realm of performance. Anonymous PL/SQL
blocks are stored in the APEX metadata as uncompiled PL/SQL code. Each time
they're required to run, they must first be extracted from the APEX metadata, parsed,
compiled, and then run. This process carries quite an overhead if the PL/SQL in ques-
tion is part of a page that gets thousands or even hundreds of thousands of hits a day. If
you move that code into a stored program unit in the database, the retrieval, parse, and
compile steps are all skipped, and the code is run directly.
Another benefit is reusability. If the same logic is used in more than one place, it can
simply be called instead of duplicated in two anonymous blocks. Therefore, any
change to the business logic need only happen in one place. Another reusability benefit
might occur if multiple systems (some being non-APEX) need access to the same busi-
ness logic. When stored in a PL/SQL program unit, it doesn't matter whether the call-
ing system is APEX, .NET, Java, or PHP. They can all use the same logic.
Finally, by moving business logic code into stored program units, you gain the abil-
ity to code, debug, and test these program units outside of the restrictions of APEX, us-
ing your favorite PL/SQL coding tool instead. However, not all code needs to be
moved out into the database. User interface logic that manages and manipulates items
on the page, such as computations, validations, and processes, is often best kept as part
of the page. Such logic is often so page specific and so small in footprint that the gain
