In the last entry in this series, we went over the “non-blog” tables in our database. Now it’s time to cover the tables that store data specific to posts or entries. We’re going to dive right into it and take a look at the most important table, BlogEntry: 
This table will contain the details specific to an entry. So Text will be the html or the text for the post. Keywords will fill the meta keywords for the entry. Short will be used if we only want to display a shortened version of the html (in order to get more click throughs on RSS feeds for example). Our entry name will match our title with the exception that it will be HTML encoded (mostly). Lastly, the BlogAuthorId has a Foreign Key (FK) constraint to the BlogUser table (as mentioned in the previous post). In addition, BlogConfigId is a FK back to the BlogConfig table as per usual. Finally, PostType is a value we might use down the road to distinguish between live posts, drafts, or anything else we might store in the table. Moving along, we’ll look at the BlogViewEntryCount table:
For now the only real point of this table is the WebCount column. This table just keeps track of the number of times this entry has been loaded via the web. Down the road, we might add additional columns to see how many times the entry was loaded from an RSS feed or linked from Facebook, or anything else we might want to track. Each entry will (hopefully) get some comments or feedback but before we can store the feedback, we need a table to store the author of each comment. Since we’ll likely have repeat commentators we don’t want to have to store their info for every comment, so we’ll have a BlogFeedbackAuthor table:
This table will store the name, email, URL fields that we’ll ask for in every comment. Then we’ll store a total number of feedbacks for the people that enter the same info repeatedly. Simple enough. Now we’ll go to the BlogFeedback table:
First we have FKs to the BlogEntry table and the BlogFeedbackAuthor table. From there we are storing details on the actual feedback such as the body, the IP address of the user that entered it, the status it’s in (we’ll tie this to an enum in the code), the title of the feedback, and the user agent of the user. The NotifyAuthorOnFeedback column will be used to figure out if we should email the feedback author when someone comments after them on the same entry. Lastly we have Feedback Type which we might use down the road to distinguish between types of feedback. Only 4 tables left (for now) so let’s go to the BlogCategory:
Nothing really special here. The important things are the Title and the IsActive field. Now we have our BlogEntryCategory table to tie entries to categories:
This table is straight FKs. Now we’ll get to BlogPage:
This table will be used to store pages we might want to host on our site with any sort of static comment. In the PageContent column we can put any html or javascript we want. Late we’ll make a way to render this so the user can add any static pages they want without having to get into code or anything like that. We’re also allowing the user to enter the Meta Keywords and Description. Finally we’ve got the Page Title (what we’ll actually put in the Title tag) and the Encoded Title (what we’ll use for the URL). Our last table (for now) will be the Blacklist table:
This table will just be used to store the addresses of anyone that wants to opt out of emails. So if someone comments with a bad address and says they want to be emailed when someone comments after them, the original person has the ability to say “I don’t want these emails!”. So we’re done (again for now) with our Database Schema. WOOT! Coming up we’ll go over the base project structure. So that you don’t have to go and create all of these tables yourself, you can download a SQL script to create all of these tables here (the SQL file is in a ZIP).
Update 6-27-2010:
BlogCategory's IsActive field should have been a bit field but was incorrectly shown as a nvarchar.
