Chapter 12.  Interacting with Databases

12. Interacting with Databases

RELATIONAL DATABASE MANAGEMENT SYSTEMS (RDBMSs) ARE CRITICAL to modern applications: They provide powerful and generalized tools for storing and managing persistent data and allow developers to focus more on the core functionality of the applications they develop.

Although RDBMSs reduce the effort required, they still do require some work. Code needs to be written to interface the application to the RDBMS, tables managed by the RDBMS need to be properly designed for the data they are required to store, and queries that operate on these tables need to be tuned for best performance.

Hard-core database administration is a specialty in and of itself, but the pervasiveness of RDBMSs means that every application developer should be familiar enough with how database systems work to spot the good designs and avoid the bad ones.

Database Terminology The term database is commonly used to refer to both various collections of persistent data and systems that manage persistent collections of data. This usage is often fine for general discussions on databases; however, it can be lacking in a more detailed discussion. Here are a few technical definitions to help sort things out: database A collection of persistent data. database management system (DBMS) A system for managing a database that takes care of things such as controlling access to the data, managing the disk-level representation of the data, and so on. relational database A database that is organized in tables. relational database management system (RDBMS) A DBMS that manages relational databases. The results of queries made on databases in the system are returned as tables. table A collection of data that is organized into two distinct parts: a single header that defines the name and type of columns of data and zero or more rows of data. For a complete glossary of database terms, see http://www.ocelot.ca/glossary.htm.

Database optimization is important because interactions with databases are commonly the largest bottleneck in an application.

Before you learn about how to structure and tune queries, it's a good idea to learn about database systems as a whole. This chapter reviews how database systems work, from the perspective of understanding how to design efficient queries. This chapter also provides a quick survey of data access patterns, covering some common patterns for mapping PHP data structures to database data. Finally, this chapter looks at some tuning techniques for speeding database interaction.