Preface: Obviously blog engines can be much more comprehensive than what I will be describing in this article. This is just something to show a few techniques to get you started.
This is a three part series, please check back soon for the next two parts (business layer and presentation layer).
The Database (Barebones)
In this blog engine, we’re only going to have two tables; blog posts and blog comments. The tables look as follows:
Blog Posts Table
| BlogPosts | ||
| BlogID | (PK, int, not null) | Internal ID of blog post |
| Title | (varchar(100), not null) | The title of the blog post |
| Summary | (varchar(200), null) | A brief description of blog post (displayed in rss lists) |
| Description | (varchar(8000), not null) | The actual content of the blog post |
| DateCreated | (datetime, not null) | Date blog post was created |
| DateUpdated | (datetime, not null) | Date blog post was last updated |
Note: We're creating this as though it can only have one blog. This model
will not support multiple blogs without adding a higher level table to track
individual blogs, but that's a pretty simple enhancement you can make.
Blog Comments Table
| BlogComments | ||
| BlogCommentID | (PK, int, not null) | Internal ID of blog post comment |
| BlogID | (FK, int, not null) | ID of blog post the comment responds to |
| Username | (varchar(100), null) | Username of user commenting (can be anonymous) |
| (varchar(100), null) | Email of user commenting (can be anonymous) | |
| URL | (varchar(250), null) | URL of trackback/additional content |
| Comment | (varchar(2000), not null) | The actual content of the blog post comment |
| DateCreated | (datetime, not null) | Date comment was created |
| DateUpdated | (datetime, not null) | Date comment was last updated (usually never changes) |
Note: An additional enhancement you may want to make is to create an approval process of comments. There are several ways to do this, one way could be to simply add a new column in the BlogComments table which toggles status of comment. An administrator could then manually change the status of comments to be approved or not.
Great, database tables are done – not very hard, right? Next, lets go ahead and write a few stored procedures to handle data manipulation.
SPROC’s
We’re going to create four simple stored procedures to handle creating a new blog post, updating an existing blog post, deleting a blog post, and finally, create a blog comment.
CreateBlogPost
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Nicholas Barger -- Create date: 04/01/2008 -- Description: Creates a new blog post -- ============================================= CREATE PROCEDURE [dbo].[spCreateBlogPost] @Title varchar(100), @Summary varchar(200), @Description varchar(8000), @BlogID int output AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.BlogPosts (Title, Summary, Description) VALUES (@Title, @Summary, @Description); SELECT @BlogID = SCOPE_IDENTITY(); END GO
DeleteBlogPost
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Nicholas Barger -- Create date: 04/01/2008 -- Description: Deletes an existing blog post -- ============================================= CREATE PROCEDURE [dbo].[spDeleteBlogPost] @BlogID int AS BEGIN SET NOCOUNT ON; DELETE FROM BlogPosts WHERE BlogID = @BlogID; END GO
UpdateBlogPost
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Nicholas Barger -- Create date: 04/01/2008 -- Description: Updates an existing blog post -- ============================================= CREATE PROCEDURE [dbo].[spUpdateBlogPost] @BlogID int, @Title varchar(100), @Description varchar(8000), @Summary varchar(200) = null AS BEGIN SET NOCOUNT ON; UPDATE dbo.BlogPosts SET DateUpdated = getDate(), Title = @Title, Summary = @Summary, Description = @Description WHERE BlogID = @BlogID; END GO
CreateBlogComment
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Nicholas Barger -- Create date: 04/01/2008 -- Description: Creates a new blog post comment -- ============================================= CREATE PROCEDURE [dbo].[spCreateBlogComment] @BlogID int, @Username varchar(100) = null, @Email varchar(100) = null, @URL varchar(250) = null, @Comment varchar(2000), @BlogCommentID int output AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.BlogComments (BlogID, Username, Email, URL, Comment) VALUES (@BlogID, @Username, @Email, @URL, @Comment); SELECT @BlogCommentID = SCOPE_IDENTITY(); END GO
Ok, we’re going to stop there for this part of the series. Please check back later when I demonstrate how we start using the database we setup!
Remember, this is not only good practice for writing a simple application, but it’s also really beneficial to develop the basis of a blog engine that you can now build any custom features you can’t get with out of the box blog services or engines. So, at the end of this series, be sure to take this code and make it your own by adding to it. Feel free to
write me back with additional features you’ve come up with in the comments below!
Nicholas Barger's Blog 
One thought on “An Example of Writing Your Own Blog Engine (mini-version)”