How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (2)

Last time in “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (1)”, I gave you an introduction.

This time I will elaborate on the database structure.

Project folder layout

The following top level directories may exist for every database application project:

Directory apex

When you invoke on Windows:

tree /A apex

you will get something like this (items between ‘${‘ and ‘}’ are variables):

\---f${application id}
    \---application
        +---pages
        +---shared_components
        |   +---files
        |   +---globalization
        |   +---logic
        |   +---navigation
        |   |   +---breadcrumbs
        |   |   +---lists
        |   |   \---tabs
        |   +---plugins
        |   |   +---dynamic_action
        |   |   \---item_type
        |   +---security
        |   |   +---app_access_control
        |   |   +---authentications
        |   |   \---authorizations
        |   \---user_interface
        |       +---lovs
        |       +---shortcuts
        |       \---templates
        |           +---breadcrumb
        |           +---button
        |           +---calendar
        |           +---label
        |           +---list
        |           +---page
        |           +---region
        |           \---report
        \---user_interfaces

Directory db

tree /A db

returns:

+---${support schema}
|   \---src
|       +---dml
|       +---full
|       \---incr
+---${API schema}
|   \---src
|       +---admin
|       +---dml
|       +---full
|       +---incr
|       \---util
+---${DATA schema}
|   \---src
|       +---admin
|       +---dml
|       +---full
|       \---incr
+---${EXT schema}
|   +---src
|   |   +---admin
|   |   +---dml
|   |   +---full
|   |   \---incr
|   \---test
\---${UI schema}
    \---src
        +---admin
        +---dml
        +---full
        \---incr

Some explanation:

• the support schema directory contains scripts that let the application work with support software. So you can think of another schema that contains generic error packages or packages to manipulate APEX text messages and in this directory you will add the necessary grant scripts. The support software itself is maintained by another project.
• the admin directories contain scripts to setup a schema by a DBA.
• dml directories contain scripts to change reference data, for instance APEX text messages or a list of countries.
• full directories contain (repeatable) Flyway database migration scripts that are run every time they change (for a database). They are meant for database objects that can be replaced (CREATE OR REPLACE ...).
• incr directories contain (incremental) Flyway database migration scripts that will run only once (for a database), so for objects that can not be replaced like tables and constraints or for dropping objects.

Later on in the Flyway post, I will explain in more detail the naming conventions for the Flyway migration scripts.

DATA schema

This is the schema that contains the data: the tables and all objects needed to maintain the data logic. You may decide to put data logic packages in the API layer but that is up to you.

It is up to you how to create the database migration scripts for this layer but I suggest that you do NOT maintain them in SQL Developer Data Modeler since that tool is best used to initially setup the table structure and that is all. You may use it later on to generate incremental scripts but it is much easier to just use your favorite editor to modify these scripts.

Later on in the SQL Developer Data Modeler post, I will explain in more detail what scripts to create and where to save it in your project.

API schema

This is the schema that contains the business logic. It may contain data logic packages if you do not want to have packages in the data layer.

Usually these scripts are not generated by SQL Developer Data Modeler.

UI schema

All User Interface logic. This means that this schema will be the parsing schema for APEX. Please note that you can have more than one parsing schema per APEX workspace so there is no problem having several applications with different parsing schemas in a workspace.

Usually these scripts are not generated by SQL Developer Data Modeler.

EXT schema

This is an EXTernal layer I have added to the structure. It is meant for external logic: interfaces or data conversions. Please note that setting up a new system almost always requires you to import data from another source. This layer can take care of that. If your application has to support several customers you may even have a layer for each customer. The level of this layer is the same as the API layer. It can interact with the API layer in a bidirectional way. After all, the external layer may use business logic and business logic may use an interface from this layer. The UI layer may use objects from this layer too.

Usually these scripts are not generated by SQL Developer Data Modeler.

Conclusion

In this post you have seen the folder layout for your project. Later on in the posts for SQL Developer Data Modeler, Flyway and Maven, I will add more detail.

Stay tuned!

All articles in this serie