Designing the API
Tables and triggers
reading data directly from tables (views)
writing data directly to tables (views)
using triggers for changing related tables
the application must be aware of the data model
this approach provides maximum flexibility
hard to maintain consistency
Functions
reading data via table functions
writing data by calling functions
the application is separated from the data model and is limited by API
lots of wrapper functions required
potential performance issues
There are several ways to set up an API.
The first option is to allow the application to access and modify database
tables directly. In this case, the application must have the exact knowledge
of the data model. This requirement can be relaxed to some extent by using
views.
Another limitation of this approach is that the application has to follow certain
rules; otherwise, it is very hard to maintain data consistency if you have to
address all possible inappropriate operations at the database level. But this
is the approach that provides the most flexibility.
Another option is to forbid direct table access from the application and allow
only function calls. Reading data can be performed by table functions (which
return a set of rows). Writing can be performed by calling other functions and
passing the required data to them. In this case, all the necessary
consistency checks can be implemented within functions: the database will
be protected, but the application will be able to use only a limited set of
features that we provide. It requires writing many wrapper functions and can
lead to performance degradation.
You can also combine these two approaches. For example, you can allow
the application to read data from tables directly, but perform modifications
only by functions.