Speed versus Scale

The tagline across many AI systems and low-code application platforms goes something like this, “transform your spreadsheet into a web app in minutes!”. This approach is perfect for one-time usage and events which only require basic CRUD operations to be supported. But when a column’s logic needs to be enhanced or the scope of the application grows, you need a more adaptable approach.

3 Key Takeaways ✍️

In case you are pressed for time, here’s how I approach designing APEX apps for longevity:

1. Start with a Robust Data Model

2. Leverage Database Views

3. Build Configurability into Key Variables

Use Case

In a previous blog I described the situation where my client, with over 600 legacy systems in place, was launching an ERP transformation project. We needed an artifact to capture our analysis of how the new ERP would impact each system -would the legacy app now be replaced, retired, updated or upgraded?

Most consulting companies, including the Big 4, use spreadsheets to capture their analysis, and our team did the same.

Please Say ⛔ To Spreadsheets for any System of Record Work

Quickly, we ran into the issues described in Key Takeaway Point # 2 of my previous blog on building a Systems Assessment Application. No worries - Oracle APEX to the rescue. In less than 5 minutes, I built an APEX app based on the project’s spreadsheet implementing the Speedy Approach below. The team crowned me hero for the day 👑.

Then, came the request for additional features. Oh, I wished I had taken the more Scalable approach, described further below.

The Speedy Approach

This is a perfect path for one-time use cases. Simply allow APEX to convert your Excel sheet to a web app (here's how).

Within four minutes, APEX created four pages automatically for me:

1. Home Page

2. Dashboard - displays key metrics and trends at a glance

3. SysAssessment Search - to filter and search to my heart’s content

4. SysAssessment Report - an Excel-like report view which allows me to create new and edit existing records.

Home Page is illustrated below. Refer to my previous blog to view additional pages from the app.

☝🏼Quick Tip

☝🏼Quick Tip

A More Future-Proof Approach

It’s rare for an APEX app to remain static, particularly in the enterprise. As soon as usage grows, users start asking for new features and enhancements. That’s why an app needs to be designed to evolve.

Over time, I’ve learned to build every app with three foundational pillars that make it more future-ready and adaptable. Here’s how I approach designing APEX apps for longevity:

1. Start with a Robust Data Model

2. Leverage Database Views

3. Build Configurability into Key Variables

1. Start with a Robust Data Model

Do not model data until you can answer “I know what each record represents.” Model why the data is being collected, not just what. For my use case (the systems assessment app above), the why is clear:

from my previous blog’s conclusion: a system assessment reveals the changes that need to be made in your environment to make the most out of the new ERP. It produces a Decision Matrix highlighting the systems that need to be upgraded, retired or replaced and applications that need to remain to fill gaps the ERP cannot address.

Next, think through what data elements to segment the application by. I knew I wanted to partition by:

1. Client - as a consultant, I want the flexibility to reuse this application across multiple clients; multi-tenancy to allow to filter by customer or lines of business within a client

2. Event - I want the ability to run multiple assessments at the same client site, not just one, e.g., 2024 Assessment, TEST, Simulation v1, Simulation v2, etc.

With these two in mind, here’s my 2-minute data model:

After creating my own ERD, I thought I would ask AI to give it a try. See my attempts below with both GPT-5.1 and Claude’s 4.5.

👇🏼 🤖Here’s GPT-5.1’s 2-minute data model:

👇🔥And… here’s Claude’s 2-minute data model:

This is the prompt I used for both AI systems:

You are a data model design expert for Oracle APEX enterprise applications. Review the attached spreadsheet showing a systems assessment—a company exercise that produces a Decision Matrix for legacy IT systems before the start of an ERP implementation.

A systems assessment evaluates legacy applications across three dimensions:

1. ERP Compatibility: Redundancy analysis, integration capabilities, data touchpoints

2. Risk Assessment: Compliance, security, vendor support status

3. Sustainability: Integration-readiness, scalability, total cost of ownership

Deliverables:

1. A multi-tenant data model in Mermaid ERD syntax

2. SQL DDL scripts for Oracle APEX

3. Sample CSV data (3 clients, 2 assessment events each, 450 systems per event)

The data model should support these goals:

1. Support multiple clients, each with multiple assessment events.

2. Each client is a tenant. Tenants cannot see each other’s data.

3. Allow clients to create, rename or retire assessment criteria without schema changes.

4. Allow dynamic rating scales (e.g., Yes/No, 1–5, 0/0.25/0.5/1) by criteria.

5. Track every rating, including rater, rating value, scale version, timestamp, and comments.

6. Maintain full lineage and auditability.

7. Allow reporting across events, clients, and systems.

8. Create and maintain multiple events of a systems assessment by client.

A couple of observations:

• Each AI system produced a more comprehensive data model in two minutes than I ever could.

• Each data model solves for the relationships between the rating scales, values, ratings and weights. Again, this is something that would have taken me numerous iterations to solve.

So, where could this go wrong?

Solely relying on AI-generated data models can be risky because they don’t understand your business context, infrastructure or edge cases. They may design poor indexes, over-normalized tables and not account for performance. Worse, I’ve seen AI miss critical security considerations, like allowing client’s to see each other’s data.

Bottom line: Treat the AI-generated data model as a high-quality first draft. Know that it will require a high level of validation and iteration to finalize. Only you and your domain experts can validate whether it is a true fit for your environment.

NOTE: It took eight iterations to arrive at my final prompt. With the release of GPT-5.1 and its new 5.1 Prompting Guide, which emphasizes that this latest version follows instructions more literally, it’s essential that prompts do not contain any conflicting instructions, else, the AI will try to resolve them.

Mitigating data model risks

Here’s what I do to validate my application’s data model:

1. Go deep on the requirements. Drill down two to three levels deep on each one. Challenge every requirement: Does it always apply? What about in x situation? What business conditions could make it obsolete?

2. Capture real use cases. Include high-risk and exception scenarios. I test every business rule against edge cases like nulls, duplicates and boundary conditions.

3. Performance test. Load test with high volume and concurrent users.

4. Multi-Tenant Isolation. Verify tenants cannot access each other’s data.

5. Have a DBA review the schema. For example, my project’s DBA warned that the criteria / ratings design suggested by the two AI systems above, will likely not scale to thousands of systems. He recommended materialized views or de-normalization for better performance.

Validation is grueling work. Your years of experience have built an intuition for spotting errors. Your client is paying exactly for this expertise. Push yourself hard here. It pays dividends in time and rework saved down the road.

2. Leverage Database Views

Different APEX pages and reports interact with the data in your app in different ways. Instead of writing a complex query in every APEX page, you create a view once and then just reference it where needed. The view handles all the complexity behind the scenes. For example,

• Maintain less code - With a view, you can write complex calculations and combine data from multiple tables once - then reference the view multiple times across your app. When a business requirement changes, make the change in the view rather than having to make the same update in 20 different pages.

• Store business / security rules in one place - Instead of repeating the same filters and access rules on every APEX page, build them once into your view. All pages automatically enforce the rules, and when you need to update the security, you only have to change it in one place.

• Compute-On-The-Fly - You can introduce creative logic in a view so you do not have to add physical columns to your tables. Build the logic once, apply everywhere. This also helps keep your base tables clean and normalized.

3. Build Configurability into Key Variables

Instead of hard-coding variables, turn them into Lookup tables so they can be updated without having to update code.

For example, the screenshot below shows how variables that would typically be hard-coded were converted into Lookups for a Document Generator solution. The URLs, object store bucket names and file paths are now configurable. If the client wants to change the object store bucket the Document Generator uses, they simply update the Lookup value - no code change required.

Conclusion

Building scalable Oracle APEX apps requires moving beyond the quick “spreadsheet-to-app” conversions. Investing the time upfront in designing a well-validated data model, using views to centralize security and business logic and storing key variables in lookup tables instead of hard-coding them, you enable your applications to scale and adapt as your business evolves. Your future self will thank you :)