If you think about a web application, you don’t really think about the database behind it. The same should be true of an Apex application. The application should really just be a presentation layer that has no real knowledge of the database structures. It should be able to be switch for some other web technology, such as Oracle Jet without significantly altering the database structures.

When an application is separated from the data objects in the database, your application is even more secure.

The first schema is the data schema.  It contains the data tables as well as the business logic in packages. For now let’s refer to it as XXBASE This schema forms the first layer in our structure.

The second schema we will call XXEXT since it is really and external layer that can be accessed by an application, web service or any other external system.

The XXEXT schema will only have views and packages in it. It is this layer that will access the data in XXBASE. Nothing outside of this layer, even your Apex application, will have direct access to XXBASE.

The third and final schema, which we will call XXAPP, is the schema against which your Apex application is parsed. It will be a very light schema as it will not contain any objects. It will be granted access to XXEXT using the principle of least privilege.

Now let’s take a look at how we make use of these three layers.

Suppose we are creating a report in our application that gets data from a table called CUSTOMERS. We would probably have written something like this:

Select customer id, customer name, customer_address || ‘ : ‘ || city, phone_number

From customers;

We have specified specific columns and have done a concatenation of the address with the city. The same query is used elsewhere in our application and each time we are doing the same concatenation.

The report looks great and we are happy with it as are the business users.

Now, imagine that business has a new requirement and want the country to be included in our concatenation. We have to search through our application and find everywhere that we have used the concatenation and change it. And the next time there is any change needed in the report, we have to work our way to find the place where we have to make the change. In a large application this might be quite a lengthy task.

But what if instead of selecting directly from the table, we create a view for this report and select from the view.

For this example will name the view  xx customer report v; or something similar. Now the query in the application would simply look like this:

Select *

From xx_customer_report_v;

Our concatenation is no longer in the query but instead is in the view. When business wants to add country to the address concatenation, we simply make the change in only one place and that is the view.

The view that we created would not reside in the application parsing schema XXAPP but in the middle layer or the XXEXT schema. Any of the views we create will also be in the XXEXT schema. We can create views for all of our functional areas or even one per page if that makes more sense to you.

Notice that in the example above, the application is selecting from the view only and has no idea what tables are involved nor the names of individual columns. Even if the view is complex and involves a number of tables, aggregations, averages and so on, the application layer or XXAPP has absolutely no idea what is involved in the data set.

Sounds like a lot of work doesn’t it. Maybe in the beginning it is, but as you go along it will make sense and go faster than you might expect.

So what about DML statements? In the case of insert, update delete, we would create a packaged procedure in XXEXT that receives the data we want to submit. We can do this by passing the values as a record to the procedure. This then becomes our table API. The packaged procedures now look after the DML and return a status and error message if required.

The DML process from our application now only makes a simple call to the packaged API procedure and let’s the database take care of the rest. Our application code is very simple keeping the application a thin presentation remains very thin which after all is our goal.

One thing that you must keep in mind with this approach for DML is that cannot use the Apex wizards to help you as the form processing will be wrong. You can use the wizard to get the needed items on to the page, but cannot use the processing that is generated.

You must also be aware that this approach to DML means that you have to handle read write consistency yourself that would have previously been handled through the wizard. We won’t go into that here, but you can find other articles and blog posts on the subject.

We have now successfully separated our application from both the business logic and the data objects. Although this approach may have you spending more time in the database rather than on the application, the benefits in the long run far outweigh the effort that this approach may take.

Many senior Apex consultants would recommend this approach or something quite similar to it. It makes your application far more flexible and creates a structure that can by other external systems or web services. Whenever you want to make data available from XXBASE you create a view in XXEXT. The presentation layer is isolated from the base database objects in a way that makes our application more supportable, robust and scalable.

Pin It

Contact Us

Please let us know your name.
Please let us know your email address.
Invalid Input
Please write a subject for your message.
Please let us know your message.
Invalid Input

Contact Us

Our Address

AppLinks (Pty) Limited
20 Wildebees Street
Alberton, South Africa
+27 (0) 11 902 3688
+27 (0) 83 276 3315