Now that we’ve covered what this project is all about, we’ll get into the database design for our blog engine, Veritas. So when you think of a blog, what are some of the things you see across the board?
- Blog entries
- Categories
- Images
- Comments
Chances are good those would be on everyone’s list. These are certainly the minimum of what we’d need to track in the database. However, since we’re not JUST making a site that’s going to display information from a database (it does have to provide functionality too) we have to go a bit deeper. Instead of just those 4 things, our database will need to track these things as well:
- Configuration settings
- Number of views
- Error / audit logs
- Users
- Roles
- Content Pages
The last one, “Content Pages”, might not sound familiar. In addition to just putting up blog entries we’re going to want to give authors the ability to add pages with html content without having to go into the code base to add something. We’re going to split this into two separate entries: the support tables and the entry tables. So let’s get into our database!
Everything starts with our Configuration table “BlogConfig”: 
This table stores a undetermined amount of configuration settings in it’s ConfigXml column, the config ID, and a host name. We’re going to use an XML column so that we don’t have to make a database change every time we realize there is another configuration option we want to store. The config ID is going to be used in a Foreign Key (FK) constraint in nearly all of our other tables. Finally, the host will specify what website the config is for. So for this site, the host would be “ChrisRisner.com”. Now you might be wondering, what’s the point of the FK constraint back to config ID and the host name being here. Well, if we wanted to host multiple sites using the same database, this set up allows us to do so. This comes in very handy when you’re web host only gives you 1 (or few) SQL databases but you want to host multiple blogs.
Next up, we’ll look at users and roles. While you don’t necessarily need users and roles in your blog (you could always just manually insert entries into the database) it will help us with everything from connecting with Live Writer to setting up our admin section and more. So here is BlogUser:
So most of these columns should make sense. The Config ID is an FK back to our Config table. About will be used if we want to have a page that talks about the Blog User (on an About Us page for instance). NotifyForFeedback will be checked when someone comments on the user’s post (if it’s true, then we’d notify the user on a comment). Next we have BlogRole:
This table just stores the names of roles we’ve got. It’s pretty simple so we won’t go over things. Next we have the mapping table between those two, BlogUserRole:
Continuing along with our “Support tables” we’ve got the log table, BlogLog. This will be used to store exception information, security audits, etc:
Again, nothing really important or surprising here. Next we have a table we’ll use to store our menu items, BlogMenuItem:
Some of the columns here might need an explanation. Basically, each menu item can link to one of our MVC views, a specific URL, or can contain it’s own content. So if down the road we want to add a quick link with static html, we could place that in the PageContent of the menu item. Lastly (for now) is our BlogMedia table:
This table will store records of any files we upload using our upload page. The file is the actual path of the file on the hosting computer. So it would probably be something like “D:\hosting\myaccountname\something\filename.type”. The ServerPath is the web path to the file. So now we’ve covered the all of our Support tables. In the next entry we’ll go over all of the tables that actually store stuff related to our Blog Entries.
