One of the greatest concerns of Oracle APEX developers and database architects is deciding on what database architecture to run Oracle APEX on. There are a few database architectures you could use, but as you could rightly guess, they’re not all created equal, and choosing the wrong one can severely impact the efficiency and scalability of whatever APEX app you develop.
But before going into all of those details, let’s see why choosing the right Oracle APEX database is one of the most crucial points to address as an APEX developer.
What is Oracle APEX?
Oracle APEX is a low-code application development platform for building fully functional and enterprise-level applications with little or no coding.
Using Oracle APEX, you can build highly secure mobile apps and web apps that can be deployed anywhere, whether on the cloud or on-premises. Just drag and drop features and you’ll have a functional app in no time—no need to bother yourself about manually writing a thousand lines of code.
Oracle APEX is beneficial to enterprises because it allows them to:
-
- Build and deploy apps with advanced features in a fraction of the time the traditional app development approach will take.
-
- Reduce the burden on their development team
-
- Save costs
-
- Provide solutions to real business problems in real-time, such as turning a boring spreadsheet into a personalized user-friendly app
-
- Conduct datamart reporting
-
- Share data externally
-
- And lots more
All of that is great. But there’s more; did you know that you can start using APEX if your company’s systems are already running on the Oracle Database?
APEX runs on the Oracle Database. The database acts as the data processing and storage platform for the app. And APEX comes as a free in-built tool in Oracle!
What is Oracle Database?
An Oracle database is a collection of structured data stored electronically in one place. By storing the entire data an organization possesses, the Oracle database allows users within the organization to assess and retrieve important information whenever they want, at the same time, without slowing down the system. This database also prevents unauthorized access to the data.
Notably, the Oracle database is the first database designed for enterprise grid computing. It’s also the most flexible and most cost-effective option for managing enterprise data and apps.
Hence, most enterprise organizations are already running on the Oracle database. Chances are you are too. If that’s the case, you already have Oracle APEX at your disposal.
But before you start developing applications on APEX, you’ll want to be mindful of the Oracle database architecture you already have in place. How well-structured is it?
Why Oracle APEX app must run on a well-structured database architecture
There are many reasons why your Oracle APEX app should run on a well-structured database architecture:
Scalability
You want your Oracle APEX apps to be scalable. This can only happen when the database objects are arranged—or structured-—efficiently and logically. When these objects are well-structured, there is consequently proper indexing, partitioning of data, and normalization. All of these give room for the app to be scaled when needed.
Maintenance and upgrades
When database objects are organized properly, it is easier to go in to perform routine maintenance tasks and upgrades. There’s zero chaos. Moreover, a logical structure will allow you to implement changes to database structures without affecting the app. Hence, there’ll be no downtime of the app while maintenance or upgrade is ongoing.
Security
Separating database objects from the APEX App Schema enhances security. This structure allows you to implement more granular access controls, thereby restricting unauthorized access to sensitive data.
With a well-structured database where there’s separation of database objects, access is only allowed through well-defined APIs or stored procedures.
But with a poor architecture where all the database objects reside together within the App schema, it’s easier for a user to gain unrestricted access to sensitive information probably while maintaining the app, because everything resides together.
Modularity and separation of concerns
Placing database objects in separate schemas from the APEX application allows for a clear separation of concerns. When the schemas are all separated, each schema can therefore be dedicated to a specific functionality or business logic.
Consequently, there’ll be easier modularity and each schema and functionality can be maintained independently without affecting the entire database.
Collaboration
Imagine you are a team of developers working on an application on the same Oracle Database. A well-structured database architecture will allow it so that each developer can work on a different functionality without interfering with the other developers. This is because there is separation and proper modularity.
Although all the developers can use the same database objects to collaborate on the same app development, each functionality of the app resides on a different schema. This facilitates collaboration and speed.
Understanding the best type of Oracle Database architecture
I have now talked extensively but rather subtly about what happens when there’s no proper separation within the Oracle Database architecture. Yet, I see that many developers still place everything together. They place the customer data and business logic within the Oracle APEX Application Schema (Parsing Schema). This means there’s only one schema for everything.
Below is an illustration of the said architecture:
While this approach can appear simple and efficient at first, it usually leads to numerous problems and inconveniences down the line. As such, it’s ill-advised to place the UI Logic, Customer Data, and Business Logic within the same schema. And it’s best if developers can stop this, as it can make the application difficult to manage later.
Here are the specific problems that such a database architecture can cause:
1. Security concerns
As I have already mentioned, a well-structured architecture enhances security. Conversely, a poor architecture cripples it. For starters, sensitive customer data becomes exposed. This is easy to imagine, knowing that all customer data and business logic are located in the same schema. If there’s a slight security breach in the app, the entire schema alongside the data and business logic therein may become compromised.
Secondly, it will also be difficult to implement granular access control when the entire data and logic are in the same schema. For example, a maintenance engineer running routine maintenance may be able to see sensitive customer data because the data is in the same “room” where they are conducting the maintenance.
2. Difficult to Scale and Maintain
As your enterprise grows, the need to scale your APEX application will inevitably arise. However, if your customer data and business logic are confined to a single schema, scaling becomes complicated.
The intertwining of business logic with the database schema creates a situation where modifying one element inevitably affects the other. For instance, scaling the database with new information may inadvertently disrupt the application logic. Addressing such changes within the application becomes necessary to prevent app performance issues, demanding additional time and effort on your part.
A more streamlined approach involves modular separation in the codebase. This way, any of the elements can be adjusted independently without causing repercussions in other elements, thereby simplifying the scaling and maintenance processes.
3. Challenges with testing
It’s common to conduct testing for individual components within each schema in isolation, rather than testing the entire app, business logic, and data schema as a unified whole. However, with a unified database architecture, it is difficult to isolate and test specific components independently because all elements within the app schema are tightly integrated.
4. Difficult to deploy changes
With the business logic and customer data in the same schema, it’s more challenging to deploy changes to the application, especially when there are also updates to both the business logic and Database Schema. It becomes pretty complex and errors are likely.
5. Vendor lock-in situation
Let’s face it, there may come a time in the future when you’ll need to switch to a different database system, probably due to an extensive transformation in the structure and operations of your organization. If the UI Logic, Customer Data, and Business Logic are in one schema in the Oracle database, how do you move the customer data or business logic out of Oracle? This might lead to a vendor lock-in, a situation where you’re stuck with the vendor—Oracle in this case.
The two best database architectures
Now that we know why to avoid that single schema database architecture, here are the two recommended ones:
1. Single Database architecture
In some situations, the organization may use one single physical database, while other organizations use two. If you work for an organization using a single physical database, the recommended database architecture to use involves creating three distinct schemas as follows:
-
- Data Schema
-
- API Schema
-
- UI Schema (APEX)
Here’s an illustration of this architecture:
Data Logic Schema
This schema is where all the customer data will reside. It will contain the data, tables, sequences, indexes, triggers, and packages for Data Logic. Views can also be added so that you’ll have read access to simple tables, such as the List of Values.
Although the UI layer can still utilize objects from the data layer for read access, all the business logic will not be in this schema but will pass through the API layer. By so doing, there will be a clear separation of concerns.
Advantages of the Data Schema
-
- Since the customer data is separate from the business logic, there’s a clear separation of concern.
-
- Enhanced security since access control can be implemented because the views are controlled.
-
- Since the customer data is separate, you can easily modify and scale it at any time independently.
- The principle of least privilege is enhanced when the API schema receives access to objects of the data schema. This means that the API only has access to the specific data elements and operations necessary for its functionality, reducing the risk of unauthorized access or misuse.
API Schema
The API Schema includes Packages and Views for business logic. There is an option to add some Data Logic here that was not included in the Data Schema. Similar to the Data Schema, the API Schema may also have controlled views for reading data and tables from the Data Logic Schema for the purpose of Data Manipulation Language (DML). The API Schema will be granted these Views and Tables using Oracle Roles. Because roles are often used for managing permissions at a higher level of abstraction, it becomes easier to manage and maintain access control.
Advantages of creating API Schema
-
- All the business logic is in one dedicated schema.
-
- It makes it easy for you to maintain and modify business logic independently without affecting customer data and UI Schema.
-
- With a well-defined API schema, you can reuse the same business logic across multiple UIs or applications. This promotes consistency and reduces redundancy in code.
-
- APIs provide a scalable way to expose business logic. As your application grows, you can extend or enhance functionality by adding new APIs or modifying existing ones without disrupting the underlying data or UI structures.
-
- API schemas facilitate versioning, allowing you to evolve your business logic over time without breaking existing integrations. Clients can continue to use older versions of the API until they are ready to migrate to newer versions.
-
- APIs enable an agile development process. Changes to the business logic can be implemented and deployed independently of the UI or database schema, which ultimately speeds up development cycles.
-
- Isolating business logic in APIs makes it easier to test and debug. Automated testing tools can focus specifically on the API layer, ensuring that the core functionality is working as expected.
UI Schema (APEX)
The UI Schema is the APEX parsing schema. This schema is dedicated to the app’s User Interface (UI) logic.
Advantages of having a UI schema
-
- Separates UI-related concerns from data and business logic
-
- Allows you to create multiple APEX apps in a single workspace.
-
- UI Schema has its own Packages and Views for the UI
-
- The UI Schema will be granted Views and Packages for API Schema using Oracle Roles
-
- Changes to the user interface can be made independently of the underlying data schema and API schema. This modularity makes it easier to update or modify the user interface without affecting the data or logic.
-
- APEX developers can work on the UI schema independently, designing and implementing the user interface without waiting for the back-end development to be completed.
2. Two database architecture
Okay, that’s for scenarios when the organization has a single physical database. In cases when there are two physical databases, the recommended architecture is to have:
-
- The Data Logic and API in one database;
-
- The APEX app and UI in the second database (referred to as the Application Database)
The database housing the Data Logic and API will then have three schemas:
-
- Data Schema
-
- Business logic schema
-
- DB Links Schema
The purpose of the DB Links Schema is to establish a connection between the two databases, acting as a vital link bridging their communication. Within this schema, careful adherence to the principle of least privilege is paramount. Views and Packages, essential for handling data and business logic, are selectively granted, ensuring a security-conscious approach to access permissions. This schema serves as a controlled gateway, enabling seamless and secure interaction between the connected databases while minimizing potential risks associated with data and business logic exposure.
The database architecture is illustrated below:
Let’s now explain the two databases in detail:
API and Data Database
As you already know, the API Schema contains the business logic while the Data Logic Schema contains the customer data. These two schemas go into one database to form the API and Data Database.
The difference between this database architecture and the inappropriate one I see people do is that the latter has the business logic and customer data within the Application Schema. But in the architecture I’m proposing, the Application Schema Database is separate.
However, the Application Database is still connected to the API and Data Database via database links. But the connectivity is controlled, allowing for secure access while preventing unauthorized access.
Advantages of the API and Data Database
-
- The usage of database links allows for secure access control, preventing authorized interactions between the application and backend layers (data and logic).
-
- Organizations can scale the API and data layers separately, boosting performance without affecting the APEX application itself.
Application Database
The application database serves as a repository for UI-related logic and views in any Oracle APEX environment.
The database links facilitate access from the Application Database to the API and Data database, thereby promoting a cohesive integration between the frontend and backend functionalities.
This database also contains UI-related views, which allow for an organized and efficient retrieval of data for the user interface.
There are also UI-related packages containing UI-related logic in this database. These packages provide a modular structure for handling diverse user interactions.
Advantages of the Application Database
-
- Organizations can enhance their user interface without impacting the backend
-
- By separating UI logic, the Application Database promotes a clean separation of concerns, so the frontend can function independently of the backend
-
- Consolidating all UI-related logic within the Application Database simplifies the app development process, management, and maintenance.
Wrapping it all up
As you can see, these two database architectures (the single and double) solve the challenges faced when the data and business logic are placed within the Application Schema.
Both are great, but you should choose based on the complexity of the application and scalability requirements. While the single database architecture is simpler, two databases allow for more flexibility and granular scalability of individual elements.
Need help?
Now that you’re aware of the importance of having a well-structured database architecture, do you wish to change your existing architecture so you can enjoy the benefits? Or are you looking to build a stellar database from scratch? I can help.
As a seasoned Oracle APEX app and database developer, I’ll work with you to figure out the right architecture for your business needs.
