Bill Tomczak of Grumpy Engineering gave a talk on Database basics for CMS at the CMS Expo. Those of us who admin multiple CMS sites know eventually you have to dig into a table or two to keep sites running happily. Bill provided an interesting history of relational databases (see Edgar_Codd), provided some basic database concepts and introduced a couple good database manager tools for the non-geniuses among us.

Database Basics

Bill began with the basics of database functions, using the acronym CRUD, which stands for create, retrieve, update and delete. It all boils down to these four actions he said. With this foundation he broke down the basic database components – tables, rows and columns.

Relational databases, the foundation of modern Content Management Systems, solved a number of problems of database management. Bill listed the primary advantages of relational databases:

  • Referential integrity
  • Maintain relationships
  • Prevent or planned records
  • Ensure required information

Modern Relational Databases

Bill went on to talk about modern relational databases used in today's Web Content Management Systems. He broke them down this way: 

  • Data layer (where content is stored)
  • Application layer (the content management system)
  • Presentation layer (the CMS template usually CSS)

Thinking about databases this way helps understand how information is accessed and received via a CMS. What this all means from a practical CMS users view is that databases on the server create, modify and deliver information to clients (web browsers.)Bill mentioned AJAX as a popular example of the client/server relationship. Javascript enabled with an XMLHttpRequest (XHR) can request and deliver information from servers without ever having to leave a web page.

Common Problems with Databases

Common database problems include:

  • Data Corruption
  • Relational Corruption
  • Structural Corruption

Bill said the first is typically a user error and easily corrected. The second is an application issue and usually not too hard to fix. The third however typically requires using backups to restore corrupted tables.The issue of database backups was one Bill emphasized and reiterated. He suggested two tools which allow for not only database backups but maintenance in general: