The Database Tier - MySQL

The database tier stores and retrieves data. It’s also responsible for managing updates, allowing simultaneous (concurrent) access from web servers, providing security, ensuring the integrity of data, and providing support services such as data backup. Importantly, a good database tier must allow quick and flexible access to millions upon millions of facts.

Managing data in the database tier requires complex software. Fortunately, most database management systems (DBMSs) or servers are designed so that the software complexities are hidden. To effectively use a database server, skills are required to design a database and formulate queries using the SQL language; SQL is discussed in Chapter 5. An understanding of the underlying architecture of the database server is unimportant to most users.

In this book, we use the MySQL server to manage data. It has a well-deserved reputation for speed: it can manage many millions of facts, it’s very scalable, and particularly suited to the characteristics of web database applications. Also, like PHP and Apache, MySQL is open source software. However, there are downsides to MySQL that we discuss later in this section.

The first step in successful web database application development is understanding system requirements and designing databases. We discuss techniques for modeling system requirements, converting a model into a database, and the principles of database technology in Appendix E. In this section, we focus on the database tier and introduce database software by contrasting it with other techniques for storing data. Chapters 5 and 15 cover the standards and software we use in more detail.

There are other server choices for storing data in the database tier. These include search engines, document management systems, and gateway services such as email software. Our discussions in this book focus on the MySQL server in the database tier.

A database server or DBMS searches and manages data that’s stored in databases. A database is a collection of related data, and an application can have more than one database. A database might contain a few entries that make up a simple address book of names, addresses, and phone numbers. At the other extreme, a database can contain tens or hundreds of millions of records that describe the catalog, purchases, orders, and payroll of a large company. Most web database applications have small-to medium-size databases that store thousands, or tens of thousands, of records.

Database servers are complex software. However, the important component for web database application development is the applications interface that’s used to access the database server. For all but the largest applications, understanding and configuring the internals of a database server is usually unnecessary.

SQL

The database server applications interface is accessed using SQL. It’s a standard query language that’s used to define and manipulate databases and data, and it’s supported by all popular database servers.

SQL has had a complicated life. It began at the IBM San Jose Research Laboratory in the early 1970s, where it was known as Sequel; some users still call it Sequel, though it’s more correctly referred to by the three-letter acronym, SQL. After almost 16 years of development and differing implementations, the standards organizations ANSI and ISO published an SQL standard in 1986. IBM published a different standard one year later!

Since the mid-1980s, three subsequent standards have been published by ANSI and ISO. The first, SQL-89, is the most widely, completely implemented SQL in popular database servers. Many servers implement only some features of the next release, SQL-2 or SQL-92, and almost no servers have implemented the features of the most recently approved standard, SQL-99 or SQL-3. MySQL supports the entry-level SQL92 standard and has some proprietary extensions.

Consider an SQL example. Suppose you want to store information about books in a library. You can create a table—an object that’s stored in your database—using the following statement:

CREATE TABLE books (
  title char(50),
  author char(50),
  ISBN char(50) NOT NULL,
  PRIMARY KEY (ISBN)
);

Then, you can add books to the database using statements such as:

INSERT INTO books ("Web Database Apps", "Hugh and Dave", "123-456-N");

Once you’ve added data, you can retrieve facts about the books using queries such as the following that finds the author and title of a book with a specific ISBN:

SELECT author, title FROM books WHERE ISBN = "456-789-Q";

These are only some of the features of SQL, and even these features can be used in complex ways. SQL also allows you to update and delete data and databases, and it includes many other features such as security and access management, multiuser transactions that allow many users to access the same database without corrupting the data, tools to import and export data, and powerful undo and redo features.

SQL is discussed in detail in Chapters 5 and 15.