SiteName

With any new project, a solid design is a good place to start. So often have I seen databases designed on-the-fly only for data to be shuffled around to get that "link" table added. Or worse yet, tacking on extra columns like the classic: "image1, image2, image3...".

With a bit of planning when designing your database, you can create a nice, normalised DB that is scalable for future growth.

Well, today I'll provide you with a database schema for your blog. The download, above, includes the SQL and mySQL Workbench file used to create this schema, in-case you want to modify it, and the graphic shown below.

A bit about this design

First off, this uses the InnoDB engine - which as of mySQL 5.5 is now the default engine. Why InnoDB? referential integrity of course! What's that!? In the words of Wikipedia:

Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).

This basically means that for, for example a comment, to exist the corresponding post must exist as well.

Most of the tables/fields should be self explanatory. The main table being blog_post. Some fields of interest are title_clean. This is an indexed column and is used to store your "clean" URL. If you're creating a blog you're very likely going to want to create clean URL's for SEO purposes. You'll find the suffix "clean" in other tables for the same reason.

Banner image is the name of your main article image. It's stored in the database so each article can have a named image rather than just "banner.jpg". Again, this is for SEO purposes and the same name should be used for your image alt tag.

The blog_related table is used to add "related" articles to a particular article. This is much the same as the blog_tag table. I should note that the design used to store the tags - that is, you add the tag and the post id it corresponds to - is one of 3 appropriate methods. I choose this one for the fact I simply preferred it over the other two design choices. If you don't know what I'm talking about, that's ok, just ignore this.

I'm happy to answer any question. Please leave them in the comment section below.