This is the first of a multi part series that will cover designing and building a blog backend from the ground up. The goal of this series is to have a fully functional (albeit simple) blog backend as the final product of the series. We will focus more on good programming technique and design and less on the actual coding of the blog, writing just enough to make a functional example.

This series is not about creating the next MySpace, or Drupal, or any other CMS system. Instead I hope to use this to teach good database design and programming technique.

To benefit fully from this series, I recommend that the reader has at least some familiarity with object oriented programming (Using PHP5), HTML, JavaScript, and SQL.

The basic design:

Most well designed applications share a single common trait in that they all utilize a framework that provides some layer of separation between different components. In web development, the prevalent approach is to use what is referred to as 3-tiered architecture which is also referred to as MVC (Model-View-Controller). In following good 3-tiered design, our blog will be separated into three separate layers :

Each layer will be its own separate entity. The user interface layer will not be allowed to communicate with the database layer directly, and the database layer will not even acknowledge the existence of the user interface.

All complex logic will be contained within the business logic layer, functioning as a go-between between the user interface (Which is what will get sent to the browser), and the database itself.

It may seem like an enormous amount of over-engineering to design the blog this way, but there is a method to this madness. A hypothetical example would be a site that was originally designed with database calls sprinkled all throughout the user interface. As the site grows more popular, the site developer realizes that he needs to begin upgrading the database schema to add new features, or to accommodate growth. Unfortunately, because the database code was not consolidated in one single region, this maintenance becomes a very large amount of work.

If the developer had followed a 3-tiered design during the initial implementation of his site, he would have been able to modify or completely rewrite the database layer to support his new schema and feature set. Upon completion of his upgrade, the front end and business logic would remain unmodified, and the site would continue to function as expected. This results in a very substantial decrease in maintenance time, and a overall much happier programmer.

We will begin our design from the bottom up, starting with the database itself.

The Database:

Database design can make or break an application. It is important to design your database with both scalability in mind, as well as good normalization techniques to reduce redundant data.

My initial design uses three tables. The tables will be:

• The Posts Table - This table will contain information about each new post to the blog.
• The Authors Table - This table will contain information about registered users to the blog.
• The Comments Table - This table contains information about comments to a post.

Additionally, we will cover the creation of several other tables to add additional features (Such as Configuration, or Word Filtering) in a later article.

By breaking our data out into several tables, we reduce the amount of redundant data stored, as well as making it easier to add future features. The Posts table consists of the following columns:

• PostID - This is a unique identifier that serves as the primary key of this table.
• PostDate - This is a date/time value that stores when the post was created.
• AuthorId - This holds a unique identifier for the Author of the post.
• SubjectLine - This is the post subject
• PostText - This is the actual text of the post.

Each row in the post table will represent a different post in our database. By using a primary/foreign key relationship, we can link each post with the corresponding Author in the Author table. The Author table schema is below:

• AuthorId - This is a unique identifier that serves as the primary key of this table, as well as the foreign key to the posts table.
• FirstName - This is the authors first name.
• LastName - This is the authors last name.
• UserName - This is the handle used for logging in. This is also a primary key of the table.
• Password - This would store the users password. While we will store it in plaintext for this series, this value should always be encrypted in a real blog.
• Email - This is the authors E-mail address.
• NextPost - This is a date/time value that marks when the user can create his next post. This is useful for automatic flood protection, or giving a user a "timeout".
• PostCount - This stores the number of posts the author has made.
• ProfileText - This is an optional bit of text that the user could store in his or her profile.
• IsModerator - This will be used as a Boolean flag to differentiate between a regular user and a moderator.
• IsContributor - This will be used as a Boolean flag to differentiate between a user who can posts new blog posts over one who can just comment.

At this point, we know enough about our database structure to start picturing some basic queries that can be used. For example, examine the following query:

SELECT posts.*, authors.FirstName, authors.LastName,authors.UserName FROM posts, authors WHERE posts.PostId = $postId AND posts.AuthorID = authors.AuthorID;

This query would perform a simple inner join on the two tables and pull all of the post information for a specified post (From the PHP variable $postID), as well as the authors First, Last and User Name. As you can see, with proper table design, retrieving and storing information in the database will be straightforward and easy.

The final table for our preliminary design is the comments table:

• CommentId - This is a unique identifier that serves as the primary key for this table.
• PostId - This is a foreign key reference to the PostID that this comment is associated with.
• AuthorID - This is a foreign key reference to the AuthorID that this comment is associated with.
• IsSpam - This is a Boolean flag that will be set by some anti-spam techniques that will be discussed in a future article.
• CommentDate - This is a Date/Time value that stores when the comment was created.
• CommentSubject - This is the subject of the comment.
• CommentText - This is the actual text of the comment.

For the sake of simplicity I will demonstrate how to build a flat comment system instead of a threaded one. While the overall difference internally is very small, the amount of extra code on the front end makes it not worthwhile for an example.

With this comment table in place, a query to retrieve all of the comments and the associated author information for a post would be as simple as this:

SELECT comments.*, authors.FirstName, authors.LastName,authors.UserName FROM comments, authors WHERE comments.PostId = $postId AND comments.AuthorId = authors.AuthorId; 

This concludes the first part of this series. For those of you who want to follow along, you can download a raw SQL file to create the above database HERE. The next article will cover building the database layer class in PHP.