What client/server you should use? 

It is often a controversy what client server you should choose - Java/Oracle or VB/Oracle or VB/SQL Server and so on. This article aims to decide you which would be your best bet. 

This discussion applies to mainly OLTP applications. DSS applications usually have different requirements. 

Remember, the choice of platform mainly depends on your requirement - there's no hard and fast rule! For example, it's not a wise idea to use Oracle to help your kid's homework nor Access is good choice for capturing your 24x7 database, obviously. 


First step is to choose the database. You have following options, 

Oracle 
SQL Server 
Access 
My SQL 
Sybase 
DB2 
Teradata 

etc. 

Out of these, only MySql is free. However, it's features are pretty limited. For small application it is fine, but even for medium size applications you'll stumble into problem with it. 

Contrary to popular belief, Access is not a mere desktop database. You can create commercial application using Access. Access it not only simply database, it can act as a front end as well. It's not the end, Access can act as front end of other databases eg. Oracle or SQL Server. I personally think Access is a wonderful product! 

Consider Access when your application will be small to medium size (up to 50 tables, no trigger, not more than 10 users working simultaneously, total database file size not more than 2 GB, no table contains rows more than 100 000). You can capture your business logic in Access using DAO or ADO code. 

You can use VB to act as front end of your Access database. However, unless there's some special requirement, you'd be better off with Access front end for Access database. 

Access applications can be divided into two parts - backend Access database (tables only), front end (forms, queries, reports etc.) and even middle tier (code modules)! This is useful for multi-user Access applications. 

However, Access backend can act only as file server. The benefits of Access application is several. The files are compact and you can run it in any machine which has Microsoft Office installed. For commercial applications, you can hide your proprietary code by compiling an Access application (mde file built from mdb file). 

So, Access isn't so bad! 

Backup in Access is just simply copying the database files. 

For medium size applications (10+ users, hundreds of tables, complicated database code) you can hop for SQL Server. However, it runs only on Windows computers! Unix users out of luck with SQL Server. 

SQL Server is good database. It is easier to handle (compared to Oracle), less trained people can administer it, uptime is good, features are also good. The intuitive GUI of SQL Server makes a fun to use it. VB.NET applications has special type of integration with SQL Server. 

Oracle is the current work horse in database. It's huge, expensive, feature rich. Administering an Oracle database requires lots of knowledge of the database internals. Only large companies can afford Oracle database. But it is robust. Oracle's recoverability feature is far superior than all its competitors. It's own SQL processing engine is very powerful. You can keep your main business logics inside database using stored procedures. Now Oracle has special features to support Java applications. Oracle runs on both Windows and Unix. It can support thousands of concurrent users. It's data concurrency and locking features are excellent. 

I have not worked with any other databases. However, Sybase compares with SQL Server. DB2 is mainly for main frames. Teradata is only for data warehouse applications. 

After database, there comes front end. As I already told you, several combinations are possible. 

VB + Oracle (very common) 
Forms + Oracle (very common) 
Java + Oracle (very common) 
VB + SQL Server (very common) 
Access entirely (common) 
Access + any ODBC database (not uncommon) 
Power Builder + Sybase (common but less prevalent nowadays) 
Delphi + Sybase (common but less prevalent nowadays) 
Any other front end + Any other database (don't put fences beside your imagination) 
Java + SQL Server (possible but never heard of) 
Forms + database other than Oracle (possible but impractical) 


We shall examine each combination one after another. 

Using Access entirely I have already discussed. So, no repetition any more. 

Out of two most popular front ends, viz. VB and Forms, we need to discuss them in details. 

VB is general purpose programming languages. It has matured itself for years and now with latest .NET version, it has become tremendous powerful. It now suppors full object oriented methods including polymorphism, inheritance (even visually) and encapsulation. You can separate business logic in middle tiers by using VB DLLs (dynamic link library files). 

VB as a front end is probably your only choice when you are using SQL Server. VB has a special data connection layer built exclusively for SQL Server (just because Microsoft made them both). Remember, when you are using Microsoft database, you usually need to use it end to end ie, Windows, SQL Server, VB. 

Developer 2000 is an application consisting of mainly Forms and Reports. Forms act as front end of Oracle database. Note that Forms is not a general purpose programming language. It is a tool designed to act as an interface of Oracle database. Compared to VB, front end development using Forms is much easier and faster when the database is Oracle. In forms, for basic database tasks (eg. insert, update, delete etc.) you don't need to write any code (which is not the case for VB). Features like master detail data display, combo box implementation, list of values, navigation between forms etc. are built in to Forms. 

The bottom line is, if your database is Oracle, and your application is mainly database centric, Forms should be the best choice (and Report for reports). But what does the term database centric means? Well, that's another aspect of design. 

Where do you want to keep your business logic? In database mainly, in middle tier or in client side? If your business logic is encapsulated mostly in database as stored procedures, your application is database centric. Your front end will merely act as a window to manipulate data. Advanced database features like concurrency and locking are entirely controlled by database server itself. 

Sometimes, applications are deployed in 3 tier like Browser (user interface), Application server (middle tier) and database. Here business logics are encapsulated in middle tier. In this case, the application is middle tier centric. In fact, you can develop an application using Forms + Oracle and then deploy in 3 tier using Java (your Forms form will be converted to Java forms). 

Applications using Forms + Oracle are mostly database centric. 

VB comes to take an edge over Forms, when you need some complex graphics functionality in front end or you need to do considerable amount of processing in client side (ie. away from database). In many of today's multi-tiered applications, the database is often used as data dump only (ie. just to store data nothing else). Whether this is good or bad that's another issue (will discuss shortly) but such applications should use VB rather than Forms because Forms is not suitable for too much manipulation on client side. 

Also, it has become a fashion for many applications to make it database independent for higher customer base and marketability (though such applications are not good choice as the architecture of query processing and data storage varies widely between different databases). 

So, more your application moves toward client side processing from database centric processing, VB becomes better choice compared to Forms. 

Nowadays Java is more and more being used because of its tight integrity with web. Oracle offers special memory area called Java Pool to cache java classes for faster loading during execution. For Java applications, J2EE standard is used for middle tier, usually these applications are always in Browser + J2EE + Oracle format. 

Now, whether you want to use VB or Java for Oracle database is basically your own choice. If you have pools of VB developers go for VB/ASP else go for Java. However, the trend is to go for Java now. 

Power Builder is similar to Forms for Sybase database. 

Delphi is similar to VB. But nowadays it is rarely used.