If you’ve ever had to maintain a huge site or a rather large table of data in HTML by hand, you know how difficult and time-consuming that can be. In the early days of the web, that sort of maintenance was commonplace because few web designers (myself included) could afford to license a content management system or had the skills to roll our own. Did I want to learn Perl and CGI scripting? Nah, I was content pecking away in Arachnophilia for hours on end.

As time marched on, however, we came to understand that there were benefits to be gained from separating the content of our site from the markup that surrounds it. Sounds familiar, right? In advocating web standards, we emphasize the importance of separating markup, presentation, and behavior. The same benefits apply here: sites built using CMSs are easier to maintain and update, making it far simpler to enhance and re-factor your code. All you need to hop on the CMS/framework bandwagon is a decent grasp of organization and the ability to break your content into discrete, repeatable, chunks.

As you know, there are myriad CMS and framework options, each vying for the opportunity to help you better manage your content. With so many choices, it’s hard to even know where to begin. Personally, I don’t think there’s a single piece of software that is perfect for every project. Sure, I have a handful of go-to favorites, but I think that the tools we use should always be in service of our content, so that’s where I always start.

The Content Management Problem

When Roy of Cobweb Computer Services first reached out to us and asked for our thoughts on his redesign of Norwin Alliance Church, one of the things he said by way of introducing the site was that he was considering moving it into a particularly popular blogging/content management tool to make it easier for others to assist him with updates. After poking around the site a little bit, I was floored by the massive catalogs of data he was maintaining by hand—particularly the eight years worth of audio recordings meticulously maintained in HTML tables. Truly Roy was doing God’s work here.

Looking at all of the structured data on the site got me pretty excited because I immediately saw a ton of opportunities for getting Norwin Alliance’s parishioners better access to this content. You see, in its current form, the content catalogs are completely static. Not only does that make them difficult to maintain—it also restricts a user’s ability to search those catalogs and to build collections within the content.

Sure, the content is organized chronologically—which makes sense—but what if a parishioner is looking for a particular sermon by Reverend Daniels? There is no easy easy way to restrict the results shown to all of Daniels’ sermons. Similarly, if you only wanted to browse the choral recordings, there is no way to slice the data in that manner either. If all of this content was neatly-organized in a database, simple user tasks such as these would be a snap to accomplish. In fact, you could slice and dice the data in pretty much any way imaginable, or simply provide the tools for parishioners to do it themselves.

Designing a Database Schema

Just as there are many ways to organize the data coming out of the database, there are also numerous ways you could approach organizing it within a database. Some ways are better than others and the decisions you make should strike a balance between how you plan to make use of the data and the performance needs of your website.

I’d like to walk you through how I might approach designing the database tables for the audio section of the Norwin Alliance website. As they are not my client, I am not a member of their congregation, and I don’t know any of their parishioners or site visitors, my advice will be based solely on my typical approach to projects like this. Each project is different—please don’t take my recommendations as gospel (so to speak).

A small sampling of the eight years of audio on the Norwin Alliance site.

When we take a look at the most recent collection of audio files on the site, it appears as though the content could be broken down into just two collections of data: events and recordings. The events collection would contain details of each event: the event type (e.g. Sunday Service, Connection Service), its start date, and (optionally) an end date and a special title for that event. The recordings collection would include details pertaining to each individual recording: its type (e.g. sermon, reading, music), the person or persons recorded, the title of the recording, the audio file location (or filename), and the event to which the recording belongs. If you wanted to go one step further, you could even add optional fields to the recordings to allow someone to supply the book, chapter, and verse for readings from The Bible. Figure 3 provides a simple illustration of this means of organizing the recording data, including possible field names.

A possible database schema for organizing Norwin Alliance’s audio files.

This is a simple example of a relational database schema for MySQL. We call it “relational” because the two tables are related to one another: An event has one or more recordings and each recording belongs to a single event. This relationship is called a “one-to-many” relationship, and is often shorthanded to 1:n. Different database systems have different ways of organizing data, but most popular databases (MySQL, Postgres, etc.) are relational. More information on relational databases can be found under Further Reading.

Maintaining Data Integrity

Having a solid database schema only gets you so far. After all, a database is only as good as the content it holds.

Based on the schema outlined above, there is nothing precluding someone from entering whatever content they want in any of the VARCHAR fields. VARCHAR stands for “variable character length,” meaning the content can be any length up to the established limit seen in the parentheses. For instance, someone could enter a “Sunday Service” event type and someone else could enter it as “sunday service” or even “Sunday.” As far as the database (and the CMS/framework) is concerned, those events would not be related to one another because the text string given for each event’s “type” will not match.

The integrity of the data in any database can be easily compromised by human error, so it is important to reduce the possibility that someone might make a mistake. To ensure the integrity of the data in the system, we need to enforce certain restrictions on specific fields. For instance, it would probably make sense to limit the “type” of events and recordings to certain pre-defined set of options. That will ensure common events like Sunday Service don’t appear as “Sunday Service,” “Sunday,” or “sunday” on the whim of the person entering the data.

On the database end, you can impose that sort of restriction by using an enumerated field type that only accepts a pre-defined set of values (ENUM being the MySQL field type for that). In most cases, however, it makes more sense to maintain the pre-defined set of options inside your application or CMS logic rather than in your database schema to streamline maintenance and to allow for more elegant error-handling. Regardless of where you establish your event type options, you’ll likely want to use the select element to provide those options to the user.

In some cases, however, you may want to allow users to enter whatever they want (often referred to as “free text”) while at the same time providing recommendations for what they might be trying to enter. This would come in especially handy for fields like “Performer” in the schema outlined above. In cases like that, it’s fairly likely the same content will be entered many times, but you want more flexibility than a select offers. If that’s the case, a predictive typing mechanism that provides previously entered content for that field as options might be helpful. Coincidentally, HTML5 introduces the datalist element to serve this very purpose.

Predictive typing helps you maintain data consistency without sacrificing flexibility.

There are many other ways to maintain data integrity, most of which revolve around data validation. HTML5 offers a host of new options for client-side data validation, but don’t rely on them entirely; always make sure you validate user-generated content on the server side before pushing any of it into your database.

Crafting More Manageable Templates

Once you’ve created your database tables and migrated some (or all) of your content into them, you can begin the process of exposing that content to the world via your HTML templates. Each CMS and framework is different, but here’s a simplified port of the Norwin Alliance audio data tables with comments about where CMS/framework code would go and what it would do:

<table>
  <thead>
    <tr>
      <th scope=“col”>Date</th>
      <th scope=“col”>Service</th>
      <th scope=“col”>Speaker</th>
      <th scope=“col”>Title</th>
      <th scope=“col”>Scripture</th>
      <th scope=“col”>Music</th>
    </tr>
  </thead>
  <tbody>
    <!-- Loop over each Event (in a given date range or Event Type or some other search criteria) -->
    <tr>
      <td>
        <!-- Event Start Date(–Event End Date, optionally) -->
      </td>
      <td>
        <!-- If Event Title -->
          <!-- Event Title -->
        <!-- Else -->
          <!-- Event Type -->
        <!-- End If —>
      </td>
      <td>
        <!-- If Event Has a Sermon or Lecture -->
          <!-- Sermon or Lecture Performer -->
        <!-- End If -->
      </td>
      <td>
        <!-- If Event Has a Sermon or Lecture -->
          <a href="<!-- Path to Recording File -->"><!-- Recording Title --></a>
          (<!-- File Size of Recording File -->)
        <!-- End If -->
      </td>
      <td>
        <!-- If Event Has a Reading -->
          <a href="<!-- Path to Recording File -->"><!-- Recording Title --></a>
          (<!-- File Size of Recording File -->)
        <!-- End If -->
      </td>
      <td>
        <!-- If Event Has Music -->
          <a href="<!-- Path to Recording File -->"><!-- Recording Title --></a>
          (<!-- File Size of Recording File -->)
        <!-- End If -->
      </td>
    </tr>
    <!-- End the Event loop —>
  </tbody>
</table>

Of course that’s a pretty gross oversimplification, but it demonstrates how the overall code necessary to reproduce the existing data tables could be reduced to a single template table row (tr) that gets repeated as many times as necessary to house all of the data being fetched from the database. This example demonstrates one other facet of good template design: the use of conditional logic that shows specific information based on the content being dropped into it. You can see it in the third marked section, where the event title is shown, but the event type is used as a fallback if no title is supplied. Building flexible templates like this can help you keep the DRY Principle: Don’t Repeat Yourself.

Once you’ve got a basic template setup like this, the next step (apart from filling in the necessary code to make it work) would be to make the template even more flexible by enabling it to handle multiple instances of a specific recording type (e.g. music) in each of the cells. As this is meant to be a brief introduction to the topic of content management, however, I’ll leave that as an exercise for you to undertake.

Going the Extra Mile

When you migrate your frequently-repeated content types into data objects, it opens up a world of opportunities for you to re-purpose that content in a number of different ways. The audio data we’ve extracted, for instance, could be easily re-formed into a Podcast parishioners could subscribe to by exposing the content via RSS. Similarly, the calendar of events (currently only exposed on the homepage and via a downloadable PDF), could be housed in the database and exposed as an iCal feed, allowing parishioners to access the full calendar locally in software programs like Google Calendar, iCal, Outlook, or on their mobile device.

Beyond exposing your data via alternate formats, making the move to a CMS or framework allows you to experiment with your HTML a bit more as well. For instance, it might make more sense to expose the audio information as a ordered list (ol). Being that you only really need to maintain the markup for a single instance of an event, it becomes relatively inconsequential to try new markup configurations that may be better suited to your application.

Furthermore, with a simple, templatized codebase, it also becomes even easier to enhance your markup with richer semantics. For example, you could add some HTML5 embellishment with time wrapped around event dates. Similarly, you could infuse your code with microformats like hCal and hAtom to enhance the findability of your content and make it possible for users to export content directly out of your HTML pages.

Pitfalls to Avoid

Don’t rush into adopting a particular CMS or framework; there is no “one size fits all” option.
Whenever possible, avoid repetition by creating reusable components within your chosen CMS or framework.

Things to Do

Examine your content and look for patterns; repeated content types and attributes are ripe for inclusion in a CMS.
Build flexible templates that can adapt to the context in which they are used by employing conditional logic.