After having a chance to look into a number of database designs, I
observe that people uniformly
favor design approach where the server data are 'encapsulated'. (Doesn't
it sound like guidelines
of package usage from Apllication Programming Manual?) Usually this
encapsulation implies
that tables are updated via stored procedures only. The major benefit of
this approach is clear:
applications become isolated from particular database schema
implementation, so that any changes
to the schema would propagate to the layer of those stored procedures
only.
There are still issues with this approach, where IMHO disadvantages
overweight benefits:
- We ignored queries in our discussion so far. Let's consider a
solution where
encapsulation is complete, in other words, if we perform queries via
stored functions as well. Logical
conclusion of this step would be revoking user privileges to directly
access data tables. Then,
as we cannot circumvent procedural layer, it becomes one more guarant of
data consistency.
Still, making queries via stored procedures is less than desirable.
First, we abandoned very
flexible SQL report generating capabilities. Second, returning data from
stored function is
technically not ideal with the existing JDBC/ODBC interface (on the
other hand, returning
sql query result in JDBC/ODBC is simple). For that reason executing
queries via SQL (and granting
read-only table access to the user) seems better solution. In this
compromise approach, however,
applications are not isolated from schema evolution any more.
- Techically, from the stored procedure invokation perspective,
existing interface via JDBC/ODBC
is very rudimentary. First, all the parameters need to be passes in
order. Second, only primary
datatypes are allowed. Why the first issue is inconvenience? After all,
today's most popular programming
languages requre function calls with arguments being in order as well?
The important distinction,
however, is that they perform static compile cheching, while JDBC/ODBC
postpones it until runtime.
Tables with several dosens of columns are common in enterprise
applications. Again, since only primary
datatypes are allowed in JDBC/ODBC calls, it translates into procedures
with lots of arguments. Invoking
stored procedure with tons of arguments depending on the order is
unreliable venture, even if
keeping stored procedures interfaces untouched.
There is actually a sound alternative to the 'stored procedures' (well,
'packages') encapsulation.
With introduction of 'INSTEAD OF' trigger in Oracle 8 any view becomes
updatable.
Applications, therefore, could be isolated from database via views
(surprise:). In contrast
to stored procedures, views are better fitting into JDBC/ODBC interface,
while providing
perfect encapsulation layer.
I would like if other people would share their opinions, or, please,
give me some technical
references on the subject.
Received on Tue Jan 26 1999 - 21:16:43 CST
