When to Skip Moving to a SQL-Centric DBMS
As much as we have talked about the power of SQL, we must be honest in describing those situations
where an alternative system would be better. You are probably better off staying with software that does
not require SQL (like Access) in most of these situations:
- Only one or a few people will use the database at once.
- The data can be stored within 50% of the disk capacity available to you.
- You are comfortable with the amount of media and operator time required by your backup plan.
- You do not expect to significantly expand the quantity of data or number of users.
- You do not plan to make the database available on an Internet site.
- The vast majority of the data you use is in a form that is not relational, such as files in folders.
If you the answer is yes to all of the above then a desktop solution like Microsoft Access will be cheaper
and easier to learn. Most of what you will need to do can be performed through wizards and the drag 'n'
drop interface, thus avoiding the need to invest in the hardware, software and learning for a SQL
As we discussed earlier on in this chapter, you can still use SQL with Access even
though the system does not require it. Hence why we can use a desktop system such as
Microsoft Access in order to learn SQL.
Development of a system in a SQL-centric, heavy-duty DBMS is more time-consuming than in a
drag and drop DBMS like Access. You may consider prototyping in Access to get a set of forms and
reports for the revision and approval process with your clients. Then with a model approved you can
create the final product with far fewer hours spent in coding SQL. You may even find that a lot of
the queries you made in Access can be cut and pasted into your final code.
Structured Query Language, tested and true over many years, is the standard way to communicate with
a database. SQL is almost universally understood by both front ends and back ends. Front ends present
a user interface such as Visual Basic, C++, ASP, and Microsoft Office applications. They have the
ability to connect to a datastore and send it a SQL statement. Back ends are Database Management
Systems such as Oracle, Microsoft SQL Server, Sybase and sometimes, desktop systems like Access.
Back ends can accept a SQL statement and return a result. SQL is also used for communication directly
between different DBMS without a user interface.
SQL is a set-based language rather then a procedural language. The programmer creates a single SQL
statement describing the desired result and sends it to the DBMS. The DBMS then uses internal code to
achieve that result. The internal code will vary among DBMS vendors, but that does not matter to the
SQL programmer using ANSI-SQL. Regardless of the DBMS the result will be the same.
In the next chapter we will discuss the specifics of setting up several back ends and front ends. Then we
study the code to create a connection and transport your SQL statements from front to back. We'll also
cover how to handle the results when they return to the front end.