In the Enterprise, things may be a little different. At the Enterprise level, you may have many client
machines accessing your database via an application. This application could be written in Java, Visual
Basic, C++, or any other programming language. The application talks to a middle tier. This middle tier
handles all the business logic. It accesses the database and returns requests to the client. The middle
tier is an application too. Its responsibility is to process the requests and send them to the appropriate
places.
The following is the flow in a three-tier design:
- A user uses a front-end application and issues a request.
- The request is checked for any errors and sent to the middle tier.
- The middle tier receives the request, performs any business logic, and passes it on to
the database. - The database performs its function on the request—whether it is to return a query
from a SELECT statement or update some rows. It then passes the data back to the
middle tier. - The middle tier performs any other logic that is required and returns the request to
the client. - The client receives the data and displays the results to the user.
The majority of the business logic is done at the middle tier, while simple parsing and error handling is
done on the front-end application. Generally, the database server's only responsibility is to serve up the
data that is requested or manipulate the data that it stores. In this type of architecture, the load is
balanced between all tiers. Each is responsible for its own duties. The front-end application is pretty
much a display tool—a way for the user to interact with the data. The middle tier is responsible for all
the heavy logic and also handles the requests and connections to the database. The database does
what it does best—serve up data.In a straight client-server environment, both machines must perform their own duties. In the earlier days
of client server technology, the majority of logical operations were performed at the server level. The
servers were generally much more powerful then the client machines, so the processing had to be done
on the server level or things came to a standstill. With the advent of the cheap, powerful PC, more logic
could be performed at the client level more efficiently. Today, most large applications use a three- or n-
tier design. However, for smaller applications, a middle tier may not be needed.The general flow of data in a client-server application is as follows:- The user uses a graphical application. He or she issues a request. The application
handles all the logic, parsing, and error handling and sends the finished request to
the database server. - The database server receives the request. It performs any of the needed operations
and sends the completed request back to the client. - The client performs any other operations and displays the information to the user.
This process doesn't have as many steps as the other applications, and it does have its limitations.
However, for small to mid-size applications, there really isn't a need for the complexity that the other
types of architectures possess.The final type of architecture is the straight report. This is the simplest of them all. It can be performed
from the command line of the MySQL monitor or contained in a simple shell or Perl script. All it does is
generate a report. The report can be very complex, but it generally flows as follows:- The user connects to the database and runs a query or series of queries.
- The result outputs to the screen or a file.
- The user disconnects from the database.
This type of program is not worried about a graphical interface or a user keying in data. It is simply a
report. The logic, depending on how complex the query, can be performed almost exclusively on the
database server using the functions that MySQL provides.Some examples are a batch job that prints out invoices after it finishes, and a report that is run every
day that calculates the standard deviation of test results. The uses are countless, but these types of
programs generally use the database instead of the client for their logic.