Re: Myth of the database independent applications (Was: Are you using PL/SQL)

> Most of the software projects I worked on there are three layers (some
> people call them tiers, tier does not mean
> separate servers): User Interface, Business/Application Logic and
> Database. User Interface simply does user interface, displays screens,
> gets data from the user. Database stores data, there may be some
> triggers, but database does not use any stored procedures. Business/
> Application logic is where all the logic/algorthsm etc are
> implemented. Business logic calls database to get data and update/
> insert/delete data. Thus database code is little. Using OO techniques,
> it is not all that difficult to write database classes in such as
> fashion that they are mostly database independent. Sure there will be
> some differences from one database to another, but differences are lot
> less than if one was writing stored procedures in database
> propritery language. Business logic can run on same server as
> database server then network time is reduced drastically. Middleware/
> interprocess communication provides efficient ways to communicate from
> user interface to business logic and to distribure business logic into
> many processes/threads. Business logic can use JDBC to connection to
> databases if business logic language is Java.
>
> I have worked so many projects using such architecture and performance
> has never been an issue. For many software vendors it is more
> important to be able to support many databases without writing lot of
> database specific code than reducing performance times by taking
> advantage every proprietery feature of a database.

There is also one thing that wasn't mentioned here - are you developing applications that are usable by many customers i.e. Products or you are developing app just for one customer with his unique needs. If the first choice then there is at least a bit sense to speak about database independence, if the second one, then according to my experience databse independence is absolute nonsense because I've never seen any customer changing his DB and keeping the same application. Changing the DB always included redevelopment of app always with much more and/or different functionality. Now at least one another aspect why it is worth to have as much as possible data integrity rules in DB - from my experience sooner or later every customer tries either
1) to access directly db via some SQL statements 2) create his own smaller or bigger app doing something on the same db.
And here integrity rules in db come in sight - without them there is almost 100% possibility that sooner or later your data base will be data waste.
I've done several data conversions from different data sources starting from MS Access and ending with SQL Server and Oracle. What I've always found is that without integrity rules I've got mess. The only question was how big the mess was i.e. how many rows I had with the broken logic? Fields that should be number but declared as char always had some characters, fields that should have values Y, N without proper control almost always had another values, tables without FKSes had orphans, tables with some intercolumn logic not declared in db always had this logic broken. The result for data conversion process was either
1) lose the data
2) assume some default values.
So if one is ready to accept the fact that sooner or later he will have some problems with data integrity then it is worth to forget about data integrity rules and write business logic outside the db. A summary of my thoughts about above mentioned things as well as database independant apps one can find in my article Do you have data waste or data base? at http://www.gplivna.eu/papers/data_waste_or_data_base.htm

Gints Plivna
http://www.gplivna.eu Received on Tue May 29 2007 - 04:31:27 CDT