Thanks for the question, Alex.
Asked: May 31, 2017 - 3:29 pm UTC
Last updated: June 01, 2017 - 12:54 am UTC
Version: 12.1.0.2
Viewed 1000+ times
You Asked
Hello Team,
I work on system that is a web portal containing over a hundred different APIs that customers use to interact with our products. This system is legacy, and was developed with little to no database governance. As a result, all these APIs map to only a couple of huge schemas. We are in the process of splitting them out into their own, but there are some tables used by many. The direction management wants to move is an API developed for anything anyone needs outside their own schema.
What I struggle with is how to handle this situation to cover all the bases. From a technical point of view, "just joining" is the simplest, best performing way to query the data. Best this would be in violation of the API architecture they are shooting for. They other way around leads to over complicated methods probably aren't as efficient.
As I am thinking about this, I suppose both could be true, join to the shared tables through an API? They just want to avoid "direct access" calls to tables outside their own application.
Your help is appreciated.
and Connor said...
An API that exposes the data via controlled business related functions is an excellent approach.
You can read all about that approach from a PL/SQL perspective here
https://blogs.oracle.com/plsql-and-ebr/why-use-plsql But *inside* the API (whether it be PL/SQL and some other layer) you should take advantage of all of the SQL facilities at your disposal, whether this be joins, aggregation, filtering etc etc...For data access and manipulation, there is simply no better interface than SQL.
So API's are good, but use them to implement *business* functions rather than becoming a poor substitute for SQL commands.